BOM之一————查詢指定節點及其所有子節點——————-

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '廣東省')
insert into tb values('002' , '001' , '廣州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河區')
insert into tb values('005' , '003' , '羅湖區')
insert into tb values('006' , '003' , '福田區')
insert into tb values('007' , '003' , '寶安區')
insert into tb values('008' , '007' , '西鄉鎮')
insert into tb values('009' , '007' , '龍華鎮')
insert into tb values('010' , '007' , '松崗鎮')
go

--查詢指定節點及其所有子節點的函數
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go

--調用函數查詢001(廣東省)及其所有子節點
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id  pid  name     
---- ---- ----------
001  NULL 廣東省
002  001  廣州市
003  001  深圳市
004  002  天河區
005  003  羅湖區
006  003  福田區
007  003  寶安區
008  007  西鄉鎮
009  007  龍華鎮
010  007  松崗鎮

（所影響的行數為 10 行）
*/

--調用函數查詢002(廣州市)及其所有子節點
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id  pid  name     
---- ---- ----------
002  001  廣州市
004  002  天河區

（所影響的行數為 2 行）
*/

--調用函數查詢003(深圳市)及其所有子節點
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id  pid  name     
---- ---- ----------
003  001  深圳市
005  003  羅湖區
006  003  福田區
007  003  寶安區
008  007  西鄉鎮
009  007  龍華鎮
010  007  松崗鎮

（所影響的行數為 7 行）
*/

drop table tb
drop function f_cid

--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