SQL语句实现遍历树中任意结点的所有孩子

 在存储过程或用户自定义函数中，使用SQL语句查找到树中某一个结点的所有孩子，包括他本身。

实际编程中，经常碰到类似的问题，比如要查询某个人的所有下属。

实例如下，用户自定义函数（GetUnderling），参数为任一结点ID；table1为源数据库表。

用户自定义函数源码：

CREATE FUNCTION dbo.GetUnderling
(@id varchar(4)) 
RETURNS @UnderlingTree table
([sID] varchar(4),[sName] varchar(10),[uID] varchar(4),[iLevel] int)
 AS 
BEGIN
    declare @iLevel int
    set @iLevel=0
    insert into @UnderlingTree select sID,sName,uID,@iLevel from table1 where sID=@id
    while (@@rowcount>0)
    begin
         set @iLevel=@iLevel + 1
         insert into @UnderlingTree select A.sID, A.sName,A.uID,@iLevel from table1 as A,@UnderlingTree as B where A.uID=B.sID and B.iLevel=@iLevel-1
    end
    return
END

table1表结构SQL定义

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO

CREATE TABLE [dbo].[Table1] (
 [sID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [uID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

table1测试数据导入，拷贝存为txt后导入就可以了。

"1","a",""
"10","j","5"
"11","k","5"
"12","l","6"
"13","m","7"
"14","n","8"
"2","b","1"
"3","c","1"
"4","d","2"
"5","e","2"
"6","f","3"
"7","g","3"
"8","h","4"
"9","i","4"

调用方式：select * from dbo.GetUnderling('3')