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

1、求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, --节点idpid int, --父节点idcaption varchar(50) --部门名称)insert into tb_departmentselect 1 ,0 ,'
AA'
union allselect 20 ,1 ,'
BB'
union allselect 64 ,20 ,'
CC'
union allselect 22 , 1 ,'
DD'
union allselect 23 , 22 ,'
EE'
union allselect 24 , 1 ,'
FF'
union allselect 25 , 0 ,'
GG'
union allselect 26 , 1 ,'
HH'
union allselect 27 , 25 ,'
II'
go;
with tas(select id,pid,captionfrom tb_departmentwhere caption = '
AA'
union allselect t1.id,t1.pid,t1.captionfrom tinner join tb_department t1 on t.id = t1.pid)select *from twhere not exists(select 1 from tb_department t1 where t1.pid = t.id)/*id pid caption24 1 FF26 1 HH23 22 EE64 20 CC*/

如果是sql server 2000呢，要怎么写呢：

--1.建表create table tb_department(id int, --节点idpid int, --父节点idcaption varchar(50) --部门名称)insert into tb_departmentselect 1 ,0 ,'
AA'
union allselect 20 ,1 ,'
BB'
union allselect 64 ,20 ,'
CC'
union allselect 22 , 1 ,'
DD'
union allselect 23 , 22 ,'
EE'
union allselect 24 , 1 ,'
FF'
union allselect 25 , 0 ,'
GG'
union allselect 26 , 1 ,'
HH'
union allselect 27 , 25 ,'
II'
go--2.定义表变量declare @tb table(id int, --节点idpid int, --父节点idcaption varchar(50), --部门名称level int --层级) --3.递归开始 insert into @tb select *,1 as levelfrom tb_departmentwhere caption = '
AA'
--4.递归的过程while @@ROWCOUNT > 0begin 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 twhere not exists(select 1 from tb_department t1 where t1.pid = t.id)/*id pid caption level24 1 FF 226 1 HH 264 20 CC 323 22 EE 3*/