树形结构中查询指定第几级父级以下的所有ID号

Question

--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select  1, 0,NULL
insert into BOM select  2, 1,NULL
insert into BOM select  3, 1,NULL
insert into BOM select  4, 2,NULL
insert into BOM select  5, 3,NULL
insert into BOM select  6, 5,NULL
insert into BOM select  7, 6,NULL
insert into BOM select  8, 7,NULL
insert into BOM select  9, 8,NULL
insert into BOM select 10, 9,NULL
insert into BOM select 11,10,NULL
go--创建用户定义函数
create function f_getParent(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int,@ret varchar(8000)
    set @i = 1
    insert into @t select ID,PID,@i from BOM where ID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.ID=b.PID and b.Level = @i-1
    end
    return
end
go--执行查询
select ID from dbo.f_getParent(11) order by Level desc--输出结果
/*
ID
----
1
3
5
6
7
8
9
10
11
*/--执行查询，选择最上级的4个父节点
select top 4 ID from dbo.f_getParent(11) order by Level desc
go--输出结果
/*
ID
----
1
3
5
6 
*/--删除测试数据
drop function f_getParent
drop table BOM

Answer 1

--创建处理函数,查询指定id的所有子结点，同时包含传入的id节点。楼主可适当修改，以供自己需要。
create function f_id(@id int)
returns @re table(NodeID int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.NodeID,@l from Tree a join @re b on a.ParentNodeID=b.NodeID  where b.level=@l-1
end
return
end

Answer 2

--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select  1, 0,NULL
insert into BOM select  2, 1,NULL
insert into BOM select  3, 1,NULL
insert into BOM select  4, 2,NULL
insert into BOM select  5, 3,NULL
insert into BOM select  6, 5,NULL
insert into BOM select  7, 6,NULL
insert into BOM select  8, 7,NULL
insert into BOM select  9, 8,NULL
insert into BOM select 10, 9,NULL
insert into BOM select 11,10,NULL
go--创建用户定义函数
create function f_getParent(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int,@ret varchar(8000)
    set @i = 1
    insert into @t select ID,PID,@i from BOM where ID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.ID=b.PID and b.Level = @i-1
    end
    return
end
go--执行查询
select ID from dbo.f_getParent(11) order by Level desc--输出结果
/*
ID
----
1
3
5
6
7
8
9
10
11
*/--执行查询，选择最靠近子节点的4个父节点
select top 4 ID from dbo.f_getParent(11) order by Level
go--输出结果
/*
ID
----
11
10
9
8
*/--删除测试数据
drop function f_getParent
drop table BOM