原文:在论坛中出现的比较难的sql问题：21(递归问题 检索某个节点下所有叶子节点)
最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
问题：求SQL:检索某个节点下所有叶子节点
部门表名：tb_department
id int --节点id
pid int --父节点id
caption varchar(50) --部门名称
-------------------------------------
id pid caption
----------------------------------------------
1 0 AA
20 1 BB
64 20 CC
22 1 DD
23 22 EE
24 1 FF
25 0 GG
26 1 HH
27 25
II
----------------树状结构如下----------------
--------------------------------------
问：怎么检索出某个节点下的所有最尾端的叶子节点。
例如：想检索AA节点下的所有尾端节点CC，EE,FF,HH?
我的解法，适合sql server 2005及以上的 版本：
-
create table tb_department(
-
id int, --节点id
-
pid int, --父节点id
-
caption varchar(50) --部门名称
-
)
-
-
insert into tb_department
-
select 1 ,0 ,'AA' union all
-
select 20 ,1 ,'BB' union all
-
select 64 ,20 ,'CC' union all
-
select 22 , 1 ,'DD' union all
-
select 23 , 22 ,'EE' union all
-
select 24 , 1 ,'FF' union all
-
select 25 , 0 ,'GG' union all
-
select 26 , 1 ,'HH' union all
-
select 27 , 25 ,'II'
-
go
-
-
-
;with t
-
as
-
(
-
select id,pid,caption
-
from tb_department
-
where caption = 'AA'
-
-
union all
-
-
select t1.id,t1.pid,t1.caption
-
from t
-
inner join tb_department t1
-
on t.id = t1.pid
-
)
-
-
select *
-
from t
-
where not exists(select 1 from tb_department t1 where t1.pid = t.id)
-
/*
-
id pid caption
-
24 1 FF
-
26 1 HH
-
23 22 EE
-
64 20 CC
-
*/
如果是sql server 2000呢，要怎么写呢：
-
--1.建表
-
create table tb_department(
-
id int, --节点id
-
pid int, --父节点id
-
caption varchar(50) --部门名称
-
)
-
-
insert into tb_department
-
select 1 ,0 ,'AA' union all
-
select 20 ,1 ,'BB' union all
-
select 64 ,20 ,'CC' union all
-
select 22 , 1 ,'DD' union all
-
select 23 , 22 ,'EE' union all
-
select 24 , 1 ,'FF' union all
-
select 25 , 0 ,'GG' union all
-
select 26 , 1 ,'HH' union all
-
select 27 , 25 ,'II'
-
go
-
-
-
--2.定义表变量
-
declare @tb table
-
(id int, --节点id
-
pid int, --父节点id
-
caption varchar(50), --部门名称
-
level int --层级
-
)
-
-
-
--3.递归开始
-
insert into @tb
-
select *,1 as level
-
from tb_department
-
where caption = 'AA'
-
-
-
--4.递归的过程
-
while @@ROWCOUNT > 0
-
begin
-
-
insert into @tb
-
select t1.id,t1.pid,t1.caption,level + 1
-
from @tb t
-
inner join tb_department t1
-
on t.id = t1.pid
-
where not exists(select 1 from @tb t2
-
where t.level < t2.level)
-
end
-
-
-
--5.最后查询
-
select *
-
from @tb t
-
where not exists(select 1 from tb_department t1 where t1.pid = t.id)
-
/*
-
id pid caption level
-
24 1 FF 2
-
26 1 HH 2
-
64 20 CC 3
-
23 22 EE 3
-
*/
