获取树形节根节点下面所有层级子节点

--bom结构，查找节点下所有子节点：

create table Tree(DomainID uniqueidentifier,ParentID uniqueidentifier,desn varchar(10))
insert into Tree select '00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000000','A1'
insert into Tree select '00000000-0000-0000-0000-000000000002','00000000-0000-0000-0000-000000000001','A2'
insert into Tree select '00000000-0000-0000-0000-000000000003','00000000-0000-0000-0000-000000000002','A3'
insert into Tree select '00000000-0000-0000-0000-000000000004','00000000-0000-0000-0000-000000000003','A4'
insert into Tree select '00000000-0000-0000-0000-000000000005','00000000-0000-0000-0000-000000000000','B1'
insert into Tree select '00000000-0000-0000-0000-000000000006','00000000-0000-0000-0000-000000000005','B2'
insert into Tree select '00000000-0000-0000-0000-000000000007','00000000-0000-0000-0000-000000000006','B3'
insert into Tree select '00000000-0000-0000-0000-000000000008','00000000-0000-0000-0000-000000000007','C1'
insert into Tree select '00000000-0000-0000-0000-000000000009','00000000-0000-0000-0000-000000000008','C2'

select * from Tree

--求某个节点下所有子节点：
create function find_childid(@id uniqueidentifier)
returns varchar(500)
as
begin
     declare @t table(DomainID uniqueidentifier,ParentID uniqueidentifier,desn varchar(10),lev int)
     declare @lev int
     set @lev=1
     insert into @t select *,@lev from  Tree where DomainID=@id
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.*,@lev from Tree a,@t b
          where a.ParentID=b.DomainID and b.lev=@lev-1
     end
     declare @cids varchar(500)
     select @cids=isnull(@cids+',','')+ltrim(DomainID) from @t order by lev
     return @cids
end
go

--调用函数
select * from Tree
select *,ids=dbo.find_childid(DomainID) from Tree


--得到每个节点路径：
create proc GetNodeFullPath
@id uniqueidentifier
as
select *,cast(' ' as varchar(10)) fullpath  into #Tree from Tree
DECLARE @i int,@j int
set @i=0
set @j=1
select @i='00000000-0000-0000-0000-000000000009' from #Tree
update #Tree set fullpath=DomainID 
while @j<=@i
begin
       update #Tree set fullpath=a.fullpath+','+ltrim(#Tree.DomainID) 
            from #Tree inner join #Tree a on #Tree.ParentID=a.DomainID 
       where #Tree.ParentID=@j 
       set @j=@j+1
end
select * from #Tree
go
--调用存储过程
exec GetNodeFullPath '00000000-0000-0000-0000-000000000009'
--消息 245，级别 16，状态 1，过程 GetNodeFullPath，第 8 行
--在将 varchar 值 '00000000-0000-0000-0000-000000000009' 转换成数据类型 int 时失败。

 

最简方法：

--向下获取SQL
DECLARE @ID NVARCHAR(100)
SET @ID = '00000000-0000-0000-0000-000000000032' --外部传入
;WITH DEPTS AS(
    SELECT * FROM Tree
    WHERE DomainID = @ID
    UNION ALL
    SELECT A.*
    FROM Tree A, DEPTS B
    WHERE A.ParentID = B.DomainID
)
SELECT * FROM DEPTS

 

 节点ID是INT类型的：

--bom结构，查找节点下所有子节点：

create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'

--求个节点下所有子节点：
create function f_cid(@id int)
returns varchar(500)
as
begin
     declare @t table(id int,parentid int,desn varchar(10),lev int)
     declare @lev int
     set @lev=1
     insert into @t select *,@lev from  os where id=@id
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.*,@lev from os a,@t b
          where a.parentid=b.id and b.lev=@lev-1
     end
     declare @cids varchar(500)
     select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
     return @cids
end
go

--调用函数
select *,ids=dbo.f_cid(id) from os


--得到每个节点路径：
create proc wsp2
@id int
as
select *,cast(' ' as varchar(10)) fullpath  into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id 
while @j<=@i
begin
       update #os set fullpath=a.fullpath+','+ltrim(#os.id) 
            from #os inner join #os a on #os.parentid=a.id 
       where #os.parentid=@j 
       set @j=@j+1
end
select * from #os
go
--调用存储过程
exec wsp2 1