父子節點問題

--查詢指定節點下的所有子節點:
--SQL Server2005以上版本
-->>TravyLee產生測試資料:

if OBJECT_ID('tempdb..#tb') is not null
drop table #tb
go
create table #tb
(
id int,
fid int
)
go
insert #tb
select 1,0 union all 
select 2,1 union all 
select 3,1 union all 
select 4,2 union all 
select 5,3 union all 
select 6,5 union all 
select 7,6 union all
select 8,4 union all
select 9,6
go
declare @id int
set @id=2
;with t
as
(
select * from #tb where id=@id
union all
select a.* from #tb a inner join t b
on a.fid=b.id
)
select * from t
/*
id fid
-------------
2 1
4 2
8 4
*/

--查詢指定位元組點下的所有父節點:

declare @fid int
set @fid=4
;with t
as(
select * from #tb where fid=@fid
union all
select a.* from #tb a inner join t b
on a.id=b.fid
)
select * from t order by id

/*
id fid
----------------
1 0
2 1
4 2
8 4
*/

--SQL Server 2000

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0  
insert into tb select 2,1  
insert into tb select 3,1  
insert into tb select 4,2  
insert into tb select 5,3  
insert into tb select 6,5  
insert into tb select 7,6
-->Title:尋找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
   insert @t select ID from tb where ParentID=@ParentID
   while @@rowcount<>0
   begin
      insert @t select a.ID from tb a inner join @t b
      on a.ParentID=b.id and 
      not exists(select 1 from @t where id=a.id)
   end 
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:尋找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
   insert @t select ParentID from tb where ID=@ID
   while @@rowcount!=0
   begin
     insert @t select a.ParentID from tb a inner join @t b
       on a.id=b.ParentID and 
       not exists(select 1 from @t where ParentID=a.ParentID)
   end
  return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/