网上找的sql查询子节点的数据

select sys_user_id as "子ID",sys_user_name as "名称", sys_user_manager as "父ID" from pub_sys_user
WHERE 1=1

SELECT * FROM pub_sys_user


create table tb(id int, [Name] varchar(20),parentID int )
insert into tb
select 382 , '调度员岗位', -1
union all select 383 , '基础知识', 382
union all select 390 , '电工基础' , 383
union all select 1995 , 'test知识点' , 390



create function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id sys_user_id,[sys_user_name] varchar(30),sys_user_manager int,lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from pub_sys_user where id=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.sys_user_id,a.sys_user_name,a.@lev from pub_sys_user 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

drop function f_cid

drop proc test
--得到每个节点路径：
create proc test
@id int
as
set nocount off
select *,cast(' ' as varchar(50)) fullpath into #os from tb
DECLARE @i int,@j int ,@f int
set @f=390
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 where #os.id=1995
go
--调用存储过程


exec test 1