SQLÊ÷½ÚµãÅÅÐò - MS-SQL Server / Ó¦ÓÃʵÀý

Êý¾ÝÈçÏÂ
InvCategoryID CategoryName CreateTime ParentID Path ItemType CategoryType ShowIndex 
1 Consumables 2010-04-14 0 1 0 1 2
2 Equipments 2010-04-14 0 2 2 1 1
3 Miscellaneous 2010-04-14 0 3 1 1 4
4 Other 2010-04-14 17:14:28.310 0 4 2 2 3
5 Paper 2010-04-14 18:20:34.607 1 1,5 1 1 0
6 Other 2010-04-14 18:29:53.187 1 1,6 1 1 7
7 Tabulation 2010-04-15 2 2,7 2 1 0
8 Parts 2010-04-20 15:01:05.997 2 2,8 2 1 0
9 Computer 2010-04-20 15:01:24.890 2 2,9 2 1 0
10 Other 2010-04-20 15:02:09.733 2 2,10 2 1 3

ÒªÇó²éѯ½á¹û°´ÕÕPath²úÉúÊ÷½á¹¹£¬²¢Í¬Ê±½Úµã°´ÕÕShowIndexÅÅÐò£¨Ê÷½á¹¹ÓÅÏÈ£¬Í¬¼¶½Úµã°´ÕÕShowIndexÅÅÐò£©
ÀýÈç
CategoryName Path ShowIndex 
Equipments 2 1
Tabulation 2£¬7 0
Other 2£¬10 3
Consumables 1 2



¸½±íÊý¾ÝSQL
if exists (select * from sysobjects where id = OBJECT_ID('[EMS_InventoryCategory]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [EMS_InventoryCategory]

CREATE TABLE [EMS_InventoryCategory] (
[InvCategoryID] [int] IDENTITY (1, 1) NOT NULL,
[CategoryName] [varchar] (30) NULL,
[CreateTime] [datetime] NULL DEFAULT (getdate()),
[ParentID] [int] NOT NULL DEFAULT ((0)),
[Path] [varchar] (1000) NULL,
[ItemType] [tinyint] NULL DEFAULT ((0)),
[CategoryType] [tinyint] NULL DEFAULT ((0)),
[ShowIndex] [int] NOT NULL DEFAULT