假设表得名字为tree
alter function getchild
(@id int)
returns @ret table(id int, child_id int, name varchar(2))
as
begin
insert into @ret select id, child_id, name from tree where id = @id
while @@rowcount > 0
insert into @ret
select t.id, t.child_id, t.name from
(select id, child_id, name from tree
where id in(select child_id from @ret))t
left join @ret t1
on t.id = t1.id and t.child_id = t1.child_id
where t1.id is null
return
endselect * from dbo.getchild(1)
alter function getchild
(@id int)
returns @ret table(id int, child_id int, name varchar(2))
as
begin
insert into @ret select id, child_id, name from tree where id = @id
while @@rowcount > 0
insert into @ret
select t.id, t.child_id, t.name from
(select id, child_id, name from tree
where id in(select child_id from @ret))t
left join @ret t1
on t.id = t1.id and t.child_id = t1.child_id
where t1.id is null
return
endselect * from dbo.getchild(1)
create function f_tree(@id int)
returns @temp table ([id] int,[Level] int)
as
begin
declare @level int
set @level=1
insert into @temp select @id,@level
while @@rowcount>0
begin
set @level=@level+1
insert into @temp select t.[child_id],@level
from t,@temp b
where t.id=b.id
and b.[level]=@level-1end
return
endgocreate table t(id int,name varchar(02),child_id int)
insert into t select 1,'a',2
union all select 1,'b',3
union all select 2,'c',4
union all select 5,'d',6
union all select 6,'e',7
union all select 7,'f',8
union all select 2,'g',6
union all select 3,'h',2
goselect distinct t.id,t.child_id,t.name
from t,dbo.f_tree(2) b
where t.id=b.id/*result:*/
/*
id child_id name
----------- ----------- ----
2 4 c
2 6 g
6 7 e
7 8 f
*/drop table t
drop function f_tree