create function aa(@upperid int)returns @t table (id int,upperid int,level int)asbegindeclare @i intset @i=1insert into @tselect *,@i from t where upperid=@upperidwhile @@rowcount>0beginset @i=@i+1insert into @tselect a.*,@i from t a left join @t b o

create function aa(@upperid int)returns @t table (id int,upperid int,level int)asbegindeclare @i intset @i=1insert into @tselect *,@i from t where upperid=@upperidwhile @@rowcount>0beginset @i=@i+1insert into @tselect a.*,@i from t a left join @t b o
create function aa(@upperid int)
returns @t table (id int,upperid int,level int)
as
begin
declare @i int
set @i=1
insert into @t
select *,@i from t where upperid=@upperid
while @@rowcount>0
begin
set @i=@i+1
insert into @t
select a.*,@i from t a left join @t b on a.upperid=b.id
where b.level=@i-1
end
return
end

create function aa(@upperid int)returns @t table (id int,upperid int,level int)asbegindeclare @i intset @i=1insert into @tselect *,@i from t where upperid=@upperidwhile @@rowcount>0beginset @i=@i+1insert into @tselect a.*,@i from t a left join @t b o
create function aa(@upperid int)
创建一个带有int类型的,名字叫做aa的函数
returns @t table (id int,upperid int,level int)
该函数返回一个数据表@t,包含三个字段,int类型的id,int类型的upperid和int类型的level
as
begin
as begin是函数声明和函数内容的分割线
declare @i int
声明一个int类型的变量i
set @i=1
给i赋值为1
insert into @t
select *,@i from t where upperid=@upperid
向@t表中插入数据,数据为从t表中查询出来,upperid为调用函数时的参数@upperid.
while @@rowcount>0
插入的数据行数大于0时,进行循环
begin
set @i=@i+1
@i递增1
insert into @t
select a.*,@i from t a left join @t b on a.upperid=b.id
where b.level=@i-1
向@t表中插入数据(t的所有值和@i),数据为从t表(别名为a)中查询出来,同时对@t以t表的upperid和@t表的id进行了左向外联结,同时满足t表的level=@i-1的数据.
end
结束循环
return
返回@t
end
结束函数