我在用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)