SQL 重复循环查询或追溯查询问题

Question

通过NO2， 一条条追溯查询，NO2,为D1，查询到NO1,为C1,再次通过NO2,为C1，查询到NO1,为B1,再次通过NO2,为B1，查询到NO1,为A1,

详细查看百度知道链接
[https://zhidao.baidu.com/question/205227355325978365.html?entry=qb_uhome_tagsql查询]

(https://zhidao.baidu.com/question/205227355325978365.html?entry=qb_uhome_tag "sql")

Answer 1

用递归
WITH TT AS
(SELECT NO1, NO2, NAME, STYLE, NO1 AS F1, 1 AS F2
FROM tb_JIE
WHERE NO1 NOT IN (SELECT NO2 FROM tb_JIE)
UNION ALL
SELECT tb_JIE.NO1,tb_JIE.NO2, tb_JIE.NAME, tb_JIE.STYLE, F1, F2 + 1 AS FLEVEL
FROM tb_JIE, TT
WHERE tb_JIE.NO1 = TT.NO2)
SELECT F1 AS NO1, NO2, NAME, STYLE
FROM TT T1
WHERE EXISTS (SELECT 1
FROM (SELECT F1, MAX(F2) AS F2 FROM TT GROUP BY F1) T2
WHERE T1.F1 = T2.F1
AND T1.F2 = T2.F2)
ORDER BY NAME