求救求救呀。。

[Lcindep110]

有表如下：
id name parentid
1 a 0
2 aa 1
3 aaa 2
4 aaaa 3
5 B 1
已知id是1是话,
要取出所有parentid是1的ID(可能还有子节点,有也得取出),

[vivianfdlpw]

create table A
(
id int,
name varchar(20),
parentid int
)
insert A
select 1,'a',0 union
select 2,'aa',1 union
select 3,'aaa',2 union
select 4,'aaaa',3 union
select 5,'B',1
go

--创建函数获取所有子节点
create function f_tb(@id int)
returns @tb table(id int,name varchar(20))
as
begin
insert @tb select id,name from A where parentid=@id
while @@rowcount>0
begin
insert @tb
select A.id,A.name
from A
join @tb B on A.parentid=B.id
where not exists(select 1 from @tb where id=A.id)
end

return
end
go

--测试
select * from f_tb(1) order by id

--删除测试环境
drop function f_tb
drop table A

--结果
/*
id name
----------- --------------------
2 aa
3 aaa
4 aaaa
5 B

(4 row(s) affected)
*/

[wgsasd311]

create table tb
(
id int,
name varchar(20),
parentid int
)
insert tb
select 1,'a',0 union
select 2,'aa',1 union
select 3,'aaa',2 union
select 4,'aaaa',3 union
select 5,'B',1
go



create function f_1(@id int)
returns @tb table(id int,level int)
as
begin
declare @i int
set @i=0
insert into @tb
select @id,@i
while @@rowcount>0
begin
set @i=@i+1
insert into @tb
select b.id,@i from @tb a,tb b where a.id=b.parentid and a.level=@i-1
end
return
end
go
select * from dbo.f_1(1)

drop table tb
drop function dbo.f_1

/*
id level
----------- -----------
1 0
2 1
5 1
3 2
4 3

（所影响的行数为 5 行）
*/

[Lcindep110]

谢谢..能说说其原理吗?再次谢过!