递归 子项查父项


create table tb(zth varchar(30) , fth varchar(30))
insert into tb values('1' , '2')
insert into tb values('2' , '5')
insert into tb values('5' , '8')
insert into tb values('5' , '9')
insert into tb values('1' , '3')
insert into tb values('3' , '6')
insert into tb values('6' , '10')
insert into tb values('1' , '4')
insert into tb values('4' , '7')
insert into tb values('7' , '11')
GO



我想要这样的结果
5 8
5 9
6 10
7 11
[解决办法]

with cte as(
select *,tt=1 from tb where zth='1'
union all
select t.*,c.tt+1 from tb t join cte c on c.fth=t.zth
)
select zth,fth from cte where tt=(select MAX(tt) from cte) order by zth,fth

--create table tb(zth varchar(30) , fth varchar(30)) 
--insert into tb values('1' ,  '2') 
--insert into tb values('2' , '5') 
--insert into tb values('5' , '8') 
--insert into tb values('5' , '9') 
--insert into tb values('1' , '3') 
--insert into tb values('3' , '6') 
--insert into tb values('6' , '10') 
--insert into tb values('1' , '4') 
--insert into tb values('4' , '7') 
--insert into tb values('7' , '11') 
--GO

;WITH cte 
AS 
(
SELECT *,1 AS [LEVEL]
FROM TB WHERE zth=1
UNION ALL 
SELECT b.zth,b.fth,a.[LEVEL]+1 AS [level]
FROM cte a INNER JOIN TB b ON a.fth=b.zth 
)
SELECT fth FROM cte WHERE [level]> =ALL (SELECT LEVEL FROM cte)
/*
fth
------------------------------
11
10
8
9

(4 行受影响)
*/