所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
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, --节点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
*/
2、怎么循环查询一个表 用递归吗？
有2张表B1和B2，B1是主表，
B1的BID是对应B2的B2ID，
B1和B2d是一对多的关系，
B2ID下还有以它为父节点的数据，测试数据如下：
B1
BID sname
1266 JM
1286 DM
......
B2
B2ID SID
1266 DH1500
1266 DH1592
1266 DH1595
DH1500 E89876
DH1500 E89896
联合2表查询，要得到这样的结果：
MainID SID
1266 DH1500
1266 DH1592
1266 DH1595
DH1500 E89876
DH1500 E89896
这个怎么查？求解
我的方法：
if object_id('[B1]') is not null drop table [B1]
go
create table [B1]([BID] varchar(6),[sname] varchar(2))
insert [B1]
select '1266','JM' union all
select '1286','DM'
if object_id('[B2]') is not null drop table [B2]
go
create table [B2]([B2ID] varchar(6),[SID] varchar(6))
insert [B2]
select '1266','DH1500' union all
select '1266','DH1592' union all
select '1266','DH1595' union all
select 'DH1500','E89876' union all
select 'DH1500','E89896'
go
--1.定义表变量
DECLARE @a VARCHAR(10)
SET @a='JM'
declare @tb table
([B2ID] varchar(6),
[SID] varchar(6),
level int --层级
)
--2.递归开始
insert into @tb
SELECT a.* ,1 [level]
FROM b2 a LEFT JOIN b2 b ON b.SID=a.b2id
WHERE b.b2id IS NULL AND b.SID IS NULL AND a.b2id IN (SELECT bid FROM b1 WHERE [sname]=@a)
--3.递归的过程
while @@ROWCOUNT > 0
begin
insert into @tb
select b.[B2ID],b.[SID],level + 1
from @tb t
inner join B2 b
on b.b2id =t.SID
where not exists(select 1 from @tb t2
where t.level < t2.level)
end
--4.最后查询
SELECT b2id MainID ,SID
FROM @tb
/*
MainID SID
1266 DH1500
1266 DH1592
1266 DH1595
DH1500 E89876
DH1500 E89896
*/