樹(shù)形結(jié)構(gòu)的數(shù)據(jù)存儲(chǔ)與數(shù)據(jù)庫(kù)表設(shè)計(jì)

樹(shù)形結(jié)構(gòu)一般用于無(wú)限級(jí)分類，無(wú)論你使用Java，.Net，PHP，Python等語(yǔ)言平臺(tái)進(jìn)行開(kāi)發(fā)應(yīng)用，樹(shù)形結(jié)構(gòu)都是很常用的結(jié)構(gòu)設(shè)計(jì)之一。
    本文主要解決樹(shù)形結(jié)構(gòu)的數(shù)據(jù)存儲(chǔ)和數(shù)據(jù)庫(kù)表設(shè)計(jì)。歡迎你可以根據(jù)你的應(yīng)用，提出您的見(jiàn)解！以供討論！
    樹(shù)形結(jié)構(gòu)的數(shù)據(jù)存儲(chǔ)
    樹(shù)形結(jié)構(gòu)的表結(jié)構(gòu)如下：
    /********* Object:　　Table [dbo].[Tree] ******/
    Create TABLE [dbo].[Tree] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [PID] [int] NULL ,
    [Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
    ) ON [PRIMARY]
    GO
    Create　　CLUSTERED　　INDEX [IX_Tree] ON [dbo].[Tree]([PID]) ON [PRIMARY]
    GO
    Alter TABLE [dbo].[Tree] WITH NOCHECK ADD
    CONSTRAINT [PK_Tree] PRIMARY KEY　　NONCLUSTERED
    (
    [ID]
    )　　ON [PRIMARY] ,
    CONSTRAINT [子ID不能等于父ID] CHECK ([ID] <> [PID])
    GO
    Alter TABLE [dbo].[Tree] ADD
    CONSTRAINT [FK_Tree_Tree] FOREIGN KEY
    (
    [PID]
    ) REFERENCES [dbo].[Tree] (
    [ID]
    )
    GO
    樹(shù)形結(jié)構(gòu)數(shù)據(jù)庫(kù)表查詢
    不用嵌套調(diào)用，直接用一個(gè)語(yǔ)句就可以生成樹(shù)，用臨時(shí)表寫(xiě)了一個(gè)存儲(chǔ)過(guò)程，改寫(xiě)為一個(gè)表值函數(shù)，供大家參考：
    查詢樹(shù)表語(yǔ)句的表值函數(shù)如下：
    29/********* 對(duì)象:　　用戶定義的函數(shù) dbo.fGetTreeTable ******/
    30Create FUNCTION dbo.fGetTreeTable
    31 (
    32 @ID int= null
    33 )
    34RETURNS @Tab TABLE(ID int, PID int, Name varchar(10), Lev int)
    35AS
    36 BEGIN
    37　　Declare @lev int
    38　　Set @lev=0
    39
    40　　While @lev=0 or @@ROWCount>0
    41　　Begin
    42　　 Set @Lev=@Lev+1
    43　　 Insert @Tab(ID, PID, Name, Lev)
    44　　 Select ID, PID, Name, @Lev From Tree Where (@Lev=1 and ((PID=@ID) or (@ID is null and PID is null))) or (PID in (Select ID From @Tab Where Lev=@Lev-1))
    45　　 order by ID
    46　　End
    47　　RETURN
    48 END
    49
    50GO
    51
    52--實(shí)際數(shù)據(jù)
    53Insert Tree(PID, Name) values(null, 公司)
    54Insert Tree(PID, Name) values(3, IT)
    55Insert Tree(PID, Name) values(1, Fin)
    56Insert Tree(PID, Name) values(5, XZ)
    57Insert Tree(PID, Name) values(1, HR)
    58GO
    直接查詢Select * from dbo.fGetTreeTable(null)就可以輸入所有記錄。