SQL 2000和2005 树形递归法小汇总

/*

* 本人在前辈基础上整理而成

*内容是关于递归查询的 2000 和2005不同的用法

*/

 

 

--测试数据
if OBJECT_ID('tb') is not null
drop table tb
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO
--2000的方法

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
declare @Level int
set @level=1
insert @t_level select @id,@level
while @@rowcount>0
begin
    set @level=@level+1
    insert @t_Level select tb.id,@level
     from tb join @t_level t on tb.pid=t.id
     where t.level+1=@level
   end
   return
end

select tb.*
from tb join dbo.f_cid('002') b
on tb.ID=b.id
/*
ID   PID  Name
---- ---- ----------
002  001  烟台市
004  002  招远市

*/
go
--2005的方法（CTE）

declare @n varchar(10)
set @n='002'
;with
jidian as
(
  select * from tb where ID=@n
  union all
  select t.* from jidian j join tb t on j.ID=t.PID
)
select * from jidian
go
/*
ID   PID  Name
---- ---- ----------
002  001  烟台市
004  002  招远市
*/
go
--查找指定节点的所有父节点(标准树形,即一个子节点只有一个父节点)
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
 INSERT @t_Level SELECT @ID
 SELECT @ID=PID FROM tb
 WHERE ID=@ID
  AND PID IS NOT NULL
 WHILE @@ROWCOUNT>0
 BEGIN
  INSERT @t_Level SELECT @ID
  SELECT @ID=PID FROM tb
  WHERE ID=@ID
   AND PID IS NOT NULL
 END
 RETURN
END
select tb.*
from tb join dbo.f_Pid('004') b
on tb.ID=b.id
/*
ID   PID  Name
---- ---- ----------
001  NULL 山东省
002  001  烟台市
004  002  招远市
*/
go
--2005的方法
declare @n varchar(10)
set @n='004'
;with
fujidian as
(
 select * from tb where ID=@n and PID is not null
 union all
 select a.* from tb a join fujidian f on a.ID=f.PID
)
select * from fujidian order by ID
/*
ID   PID  Name
---- ---- ----------
001  NULL 山东省
002  001  烟台市
004  002  招远市
*/