--处理函数
create function GetFather(@UID int)
returns @re table(uid int,level int)
as
begin
declare @l int
set @l=0
insert @re select puid ,@l from 资料表
where uid=@uid and puid<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select puid,@l
from 资料表 a join @re b on a.uid=b.uid and b.level=@l-1
end
return
end
go--调用实现查询
select a.* from 资料表 a join GetFather(5) b on a.uid=b.uid
create function GetFather(@UID int)
returns @re table(uid int,level int)
as
begin
declare @l int
set @l=0
insert @re select puid ,@l from 资料表
where uid=@uid and puid<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select puid,@l
from 资料表 a join @re b on a.uid=b.uid and b.level=@l-1
end
return
end
go--调用实现查询
select a.* from 资料表 a join GetFather(5) b on a.uid=b.uid
create table 资料表(UID int,UName varchar(10),PUID int)
insert 资料表 select 1,'aa',0
union all select 2,'bb',0
union all select 3,'cc',1
union all select 4,'dd',1
union all select 5,'dd',3
go--查询的函数
create function GetFather(@UID int)
returns @re table(uid int,level int)
as
begin
declare @l int
set @l=0
insert @re select puid ,@l from 资料表
where uid=@uid and puid<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select puid,@l
from 资料表 a join @re b on a.uid=b.uid and b.level=@l-1
end
return
end
go--调用实现查询
select a.* from 资料表 a join GetFather(5) b on a.uid=b.uid
go--删除测试
drop table 资料表
drop function GetFather/*--测试结果UID UName PUID
----------- ---------- -----------
1 aa 0
3 cc 1(所影响的行数为 2 行)--*/