在和同事Rock讨论中，得到一个思路，不用嵌套调用直接用一个语句就能够生成树，实事上他已做好了该程式，他用了临时表写了一个存储过程，我改写为一个表值函数，供大家参考：
表结构及表值函数如下：
查询树表语句
| 1/**//****** Object: Table [dbo].[Tree] Script Date: 2005-11-04 18:07:00 ******/ 2CREATE 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] 7GO 8 9 CREATE CLUSTERED INDEX [IX_Tree] ON [dbo].[Tree]([PID]) ON [PRIMARY] 10GO 11 12ALTER 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]) 18GO 19 20ALTER TABLE [dbo].[Tree] ADD 21 CONSTRAINT [FK_Tree_Tree] FOREIGN KEY 22 ( 23 [PID] 24 ) REFERENCES [dbo].[Tree] ( 25 [ID] 26 ) 27GO 28 29/**//****** 对象: 用户定义的函数 dbo.fGetTreeTable 脚本日期: 2005-11-04 18:07:02 ******/ 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--实际数据 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)就能够输入任何记录。
文章整理：西部数码--专业提供域名注册、虚拟主机服务
http://www.west263.com
以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!
