在与同事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)就可以输入所有记录。
数据库技术就是一坛陈年老酒，越久越香，学以致用。
