网上找的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