SQL递归查询

SQL递归查询
发表时间：2007年11月14日 14时47分6秒        评论/阅读(0/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 [table] a,@re b
  where a.id=b.id
   and b.level=@l-1
end
return
end
go
--调用函数实现查询
select * from f_pid(99)
 
--测试
--测试数据
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 行）
--*/
 
--如果不显示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