部门表名：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?
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-16 17:10:28
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[tb_department]
if object_id('[tb_department]') is not null drop table [tb_department]
go
create table [tb_department]([id] int,[pid] int,[caption] varchar(2))
insert [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'
--------------开始查询--------------------------
--CC，EE,FF,HH
;
WITH cte
AS ( SELECT * ,
1 [level]
FROM [tb_department]
WHERE [pid] = 0
AND caption = 'AA'
UNION ALL
SELECT a.id ,
a.pid ,
a.caption ,
b.[level] + 1 [level]
FROM [tb_department] a
INNER JOIN cte b ON a.pid = b.id
)
SELECT DISTINCT
caption
FROM ( SELECT caption
FROM cte
WHERE [level] IN ( SELECT MAX([level])
FROM cte )
UNION ALL
SELECT caption
FROM cte a
WHERE id NOT IN ( SELECT pid