树递归查询：求一个节点（层数不定），查询该点所有父亲接点ID？？

[lio_sy]

table
id parent_id

[zjcxc]

--查询函数
create function f_pid(
@id int
)returns @re table(id 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.parent_id,@l
from [table] a,@re b
where a.id=b.id
and b.level=@l-1
end
return
end
go

--调用函数实现查询
select * from f_pid(99)

[zjcxc]

--测试

--测试数据
create table tb(id int,parent_id int,name varchar(10))
insert tb select 1 ,0,'中国'
union all select 2 ,1,'广东'
union all select 3 ,1,'广西'
union all select 4 ,1,'四川'
union all select 5 ,2,'广州'
union all select 6 ,2,'佛山'
union all select 7 ,2,'东莞'
union all select 8 ,5,'越秀区'
union all select 9 ,5,'海珠区'
union all select 10,5,'芳村'
union all select 11,6,'禅城区'
union all select 12,6,'南海区'
union all select 13,11,'石湾'
go

--查询函数
create function f_pid(
@id int
)returns @re table(id 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.parent_id,@l
from [tb] a,@re b
where a.id=b.id
and b.level=@l-1
end
return
end
go

--调用函数实现查询
select * from f_pid(10)
go

--删除测试
drop table tb
drop function f_pid

/*--测试结果

id level
----------- -----------
10 0
5 1
2 2
1 3
0 4

（所影响的行数为 5 行）
--*/

[zjcxc]

--如果不显示0的那条,改函数为:

--查询函数
create function f_pid(
@id int
)returns @re table(id 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.parent_id,@l
from [tb] a,@re b
where a.id=b.id
and b.level=@l-1
and a.parent_id<>0
end
return
end
go