sql得到孩子节点列表

我在用sql语句编写存储过程时，得到孩子节点的语句很不好做，逻辑性比较强，csdn社区上答贴时也没有花更多时间写，今天做项目时遇到了，所以写了一个，与大家分享。

想要得到的效果：

得到的table中的记录是类似于树形结构，孩子节点紧随父节点如：

处理之前的数据：

ID          PID         LEVEL
----------- ----------- -----------
1           0           0
6           5           3
7           6           4
2           1           1
3           1           1
4           2           2
5           3           2
8           10          1
9           8           2
10          0           0
11          10          1

处理之后的数据：

ID         PID        LEVEL
---------- ---------- -----------
1          0          0
2          1          1
4          2          2
3          1          1
5          3          2
6          5          3
7          6          4
10         0          0
8          10         1
9          8          2
11         10         1

sql语句：

create table BOM(ID INT,PID INT,[LEVEL] INT)
insert into BOM select 1,0,0
insert into BOM select 6,5,3
insert into BOM select 7,6,4
insert into BOM select 2,1,1
insert into BOM select 3,1,1
insert into BOM select 4,2,2
insert into BOM select 5,3,2
insert into BOM select 8,10,1
insert into BOM select 9,8,2
insert into BOM select 10,0,0
insert into BOM select 11,10,1
go

SELECT * FROM BOM

ID          PID         LEVEL
----------- ----------- -----------
1           0           0
6           5           3
7           6           4
2           1           1
3           1           1
4           2           2
5           3           2
8           10          1
9           8           2
10          0           0
11          10          1

(11 row(s) affected)

 

--创建用户定义函数用于取每个父节点下子节点
create function f_getChild(@PID int)
returns @t table(ID VARCHAR(10),PID VARCHAR(10),[LEVEL] INT)
as
begin
    declare @i int
    declare @tb table(ID VARCHAR(10),PID VARCHAR(10),[LEVEL] INT)
    set @i = 0
    insert into @tb select ID,PID,[LEVEL] from BOM where PID = @PID
    declare @ID INT
    while @i < (select count(*) from @tb)
    begin
        declare @WID INT
        SET  @WID = (select top 1 ID from @tb where ID in (select top (@i+1) ID from @tb) order by ID DESC)
        IF(@WID=@ID)
        begin
           insert into @t 
               select top 1 * from @tb where ID in (select top (@i+1) ID from @tb) order by ID ASC
           SET  @WID = (select top 1 ID from @tb where ID in (select top (@i+1) ID from @tb) order by ID ASC)
        end
        ELSE
        BEGIN
            insert into @t 
               select top 1 * from @tb where ID in (select top (@i+1) ID from @tb) order by ID DESC
        END
     insert into @t select * from dbo.f_getChild(@WID)
        set @ID = @WID
        set @i = @i + 1
    end
    return
end
go

 

SELECT * FROM dbo.f_getChild(0)

------drop function f_getChild

ID         PID        LEVEL
---------- ---------- -----------
1          0          0
2          1          1
4          2          2
3          1          1
5          3          2
6          5          3
7          6          4
10         0          0
8          10         1
9          8          2
11         10         1

(11 row(s) affected)

 

如果想得到id为1的孩子列表： SELECT * FROM dbo.f_getChild(1)