在论坛中出现的比较难的sql问题：21(递归问题 检索某个节点下所有叶子节点)

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

问题：求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
*/