实现效果图如下：

 

创建表：

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

          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

          end

          return

     end

go

 

执行：

    select * from get_child(1)