递归 子项查父项
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 行受影响)
*/
