簡單查詢樹形結構數據庫表

　　在與同事討論中得到一個思路不用嵌套調用直接用一個語句就可以生成樹實事上他已經做好了該程序他用了臨時表寫了一個存儲過程我改寫為一個表值函數供大家參考

　　表結構及表值函數如下

　　查詢樹表語句

　　

　　/**//**** Object:  Table [dbo][Tree]    Script Date: :: ***/
 CREATE TABLE [dbo][Tree] (
  [ID] [int] IDENTITY ( ) NOT NULL
  [PID] [int] NULL
  [Name] [varchar] () 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

/**//****** 對象:  用戶定義的函數 dbofGetTreeTable   
腳本日期: :: ******/
CREATE FUNCTION dbofGetTreeTable
(
@ID int= null
)
RETURNS @Tab TABLE(ID int PID int Name varchar() Lev int)
AS
BEGIN
  Declare @lev int
  Set @lev=
 
  While @lev= or @@ROWCount>
  Begin
   Set @Lev=@Lev+
   Insert @Tab(ID PID Name Lev)
   Select ID PID Name @Lev From Tree Where (@Lev= and ((PID=@ID) or
(@ID is null and PID is null))) or (PID in (Select ID From @Tab Where Lev=@Lev))
   order by ID
  End
  RETURN
END

GO

實際數據
Insert Tree(PID Name) values(null 公司)
Insert Tree(PID Name) values( IT)
Insert Tree(PID Name) values( Fin)
Insert Tree(PID Name) values( XZ)
Insert Tree(PID Name) values( HR)
GO

　　直接查詢Select * from dbofGetTreeTable（null）就可以輸入所有記錄

From:http://tw.wingwit.com/Article/program/Oracle/201311/17837.html