求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?
[解决办法]
----------------------------------------------------------------
-- 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
FROM cte
WHERE [level] IN ( SELECT
MAX([level])
FROM cte ) )
AND a.[level] <> 1
) a
----------------结果----------------------------
/*
caption
-------
CC
EE
FF
HH
*/
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
declare @tb table
(id int, --节点id
pid int, --父节点id
caption varchar(50), --部门名称
level int
)
insert into @tb
select *,1 as level
from tb_department
where caption = 'AA'
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
select *
from @tb t
where not exists(select 1 from tb_department t1 where t1.pid = t.id)
/*
idpidcaptionlevel
241FF2
261HH2
6420CC3
2322EE3
*/
