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
来源:学生作业帮助网 编辑:作业帮 时间:2024/08/31 15:27:25
![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](/uploads/image/z/4675013-53-3.jpg?t=create+function+aa%28%40upperid+int%29returns+%40t+table+%28id+int%2Cupperid+int%2Clevel+int%29asbegindeclare+%40i+intset+%40i%3D1insert+into+%40tselect+%2A%2C%40i+from+t+where+upperid%3D%40upperidwhile+%40%40rowcount%3E0beginset+%40i%3D%40i%2B1insert+into+%40tselect+a.%2A%2C%40i+from+t+a+left+join+%40t+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
结束函数