--查询指定id的所有父
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select fatherid,@l from tb where id=@id and fatherid<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.fatherid,@l
from tb a,@re b
where a.id=b.id and b.level=@l-1 and a.fatherid<>0
end
return
end
go--调用(查询所有的父)
select a.* from tb a,f_pid(3)b where a.id=b.id
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select fatherid,@l from tb where id=@id and fatherid<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.fatherid,@l
from tb a,@re b
where a.id=b.id and b.level=@l-1 and a.fatherid<>0
end
return
end
go--调用(查询所有的父)
select a.* from tb a,f_pid(3)b where a.id=b.id
create table tb(id int,name varchar(10),fatherid int)
insert tb select 1,'a',0
union all select 2,'b',1
union all select 3,'c',2
union all select 4,'d',2
go--查询指定id的所有父
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select fatherid,@l from tb where id=@id and fatherid<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.fatherid,@l
from tb a,@re b
where a.id=b.id and b.level=@l-1 and a.fatherid<>0
end
return
end
go--调用(查询所有的父)
select a.* from tb a,f_pid(3)b where a.id=b.id
godrop table tb
drop function f_pid/*--测试结果id name fatherid
----------- ---------- -----------
1 a 0
2 b 1(所影响的行数为 2 行)
--*/