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)
*/
(
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)
*/
(
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
gocreate 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 行)
*/