实现效果图如下:
创建表:
create table t_tree
(
id int IDENTITY(1,1),
parentid int,
name varchar(10)
)
go
插入测试数据:
insert into t_tree select 0,‘‘
insert into t_tree select 1,‘‘
insert
into t_tree select 1,‘‘
insert into t_tree select 2,‘‘
insert into t_tree
select 3,‘‘
insert into t_tree select 3,‘‘
insert into t_tree select
5,‘‘
insert into t_tree select 5,‘‘
insert into t_tree select
5,‘‘
go
创建函数:
create function get_child(@id int)
returns @child
table (id int,parentid int,name varchar(10),level
int)
as
begin
declare @level
int
set
@level=0
insert
into @child select *,@level from t_tree where id=@id
while @@rowcount>0
begin
set
@level=@level+1
insert into @child select a.*,@level from t_tree a,@child b where
b.id=a.parentid and b.level=@level-1
end
return
end
go
执行:
select * from get_child(1)