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 a.*
from tb a join f_cid('002') b
on a.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 招远市
*/
实际运用：
/* 根据子节点，查询其所有父节点（基于标准树，就是每个子节点只有一个父节点 */
WITH test AS(
SELECT * FROM pl_zd_district WHERE code='360121'
UNION ALL
SELECT a.* FROM pl_zd_district a JOIN test b ON a.code=b.p_code
)
SELECT * FROM test ORDER BY code
/* 根据父节点，查询其下所有子节点 */
WITH test2 AS (
SELECT * FROM pl_zd_district WHERE code='360000'
UNION ALL
SELECT a.* FROM pl_zd_district a JOIN test2 b ON a.p_code=b.code
)
SELECT * FROM test2 ORDER BY code
