/*-- 得到指定id的子id列表 --*/
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select @id
while @@rowcount>0
insert into @re select a.id
from shu a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go--调用函数实现查询
select a.*
from shu a join dbo.f_getchildid(1) b on a.id=b.id
go
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select @id
while @@rowcount>0
insert into @re select a.id
from shu a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go--调用函数实现查询
select a.*
from shu a join dbo.f_getchildid(1) b on a.id=b.id
go
create table shu(id int ,--id
pid int) --父节点id
insert into shu
select 1,0 union all
select 2,1 union all
select 3,2 union all
select 4,0 union all
select 5,4
go/*-- 得到指定id的子id列表 --*/
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select @id
while @@rowcount>0
insert into @re select a.id
from shu a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go--调用函数实现查询
select a.*
from shu a join dbo.f_getchildid(1) b on a.id=b.id
go--删除测试
drop table shu
drop function f_getchildid/*--测试结果
id pid
----------- -----------
1 0
2 1
3 2(所影响的行数为 3 行)
--*/