--写个自定义函数
create function f_cid(
@id int
)returns @re table(groupid int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.groupid,@l
from [group] a,@re b
where a.parentid=b.groupid and b.[level]=@l-1
end
return
end
go--调用自定义函数查询groupid=3及其所有子的leaf
select *
from leaf a,f_cid(3)b
where a.parentid=b.groupid
create function f_cid(
@id int
)returns @re table(groupid int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.groupid,@l
from [group] a,@re b
where a.parentid=b.groupid and b.[level]=@l-1
end
return
end
go--调用自定义函数查询groupid=3及其所有子的leaf
select *
from leaf a,f_cid(3)b
where a.parentid=b.groupid
create function f_cid(
@id int
)returns @re table(groupid int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.groupid,@l
from [group] a,@re b
where a.parentid=b.groupid and b.[level]=@l-1
end
return
end
go--调用自定义函数查询groupid=3及其所有子的leaf
select *
from leaf a,f_cid(3)b
where a.parentid=b.groupid
我现在在用access做数据库,支持不了自定义函数。