在与同事Rock讨论中，得到一个思路，不用嵌套调用直接用一个语句就可以生成树，实事上他已经做好了该程序，他用了临时表写了一个存储过程，我改写为一个表值函数，供大家参考：表结构及表值函数如下：
查询树表语句 1 /**/ /* ***** Object: Table [dbo].[Tree] Script Date: 2005-11-04 18:07:00 ***** */ 2 CREATE TABLE [ dbo ] . [ Tree ] ( 3 [ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , 4 [ PID ] [ int ] NULL , 5 [ Name ] [ varchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL 6 ) ON [ PRIMARY ] 7 GO 8 9 CREATE CLUSTERED INDEX [ IX_Tree ] ON [ dbo ] . [ Tree ] ( [ PID ] ) ON [ PRIMARY ] 10 GO 11 12 ALTER TABLE [ dbo ] . [ Tree ] WITH NOCHECK ADD 13 CONSTRAINT [ PK_Tree ] PRIMARY KEY NONCLUSTERED 14 (15 [ ID ] 16 ) ON [ PRIMARY ] ,17 CONSTRAINT [ 子ID不能等于父ID ] CHECK ( [ ID ] <> [ PID ] )18 GO 19 20 ALTER TABLE [ dbo ] . [ Tree ] ADD 21 CONSTRAINT [ FK_Tree_Tree ] FOREIGN KEY 22 (23 [ PID ] 24 ) REFERENCES [ dbo ] . [ Tree ] (25 [ ID ] 26 )27 GO 28 29 /**/ /* ***** 对象: 用户定义的函数 dbo.fGetTreeTable 脚本日期: 2005-11-04 18:07:02 ***** */ 30 CREATE FUNCTION dbo.fGetTreeTable31 (32 @ID int = null 33 )34 RETURNS @Tab TABLE (ID int , PID int , Name varchar ( 10 ), Lev int )35 AS 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 ID46 End 47 RETURN 48 END 49 50 GO 51 52 -- 实际数据 53 Insert Tree(PID, Name) values ( null , 公司)54 Insert Tree(PID, Name) values ( 3 , IT)55 Insert Tree(PID, Name) values ( 1 , Fin)56 Insert Tree(PID, Name) values ( 5 , XZ)57 Insert Tree(PID, Name) values ( 1 , HR)58 GO 59 60直接查询Select * from dbo.fGetTreeTable(null)就可以输入所有记录。
