SQL 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  招远市
*/