SQL对递归表的查询

Question

我要对一个"递归表"做一个请求。

请在下面找到一个示例

ID    |    FATHER_ID    |    NAME
100   |    0            |  Human
101   |    0            |  Car
102   |    101          |  PORSCHE
103   |    101          |  AUDI
104   |    102          |  911
105   |    102          |  MACAN
106   |    103          |  A3
107   |    103          |  A5
...   |    ...          |

我想要一些像

PARENT_NAME   |   NAME
PORSCHE       | 911
PORSCHE       | MACAN
AUDI          | A3
AUDI          | A5
...           | ...

我可以用下面的查询进入第一层:

SELECT ID, FATHER_ID, NAME FROM LISTS WHERE FATHER_ID IN (SELECT ID FROM LISTS WHERE NAME = 'Car')

ID   |   FATHER_ID   |    NAME
102  | 101           | PORSCHE  
103  | 101           | AUDI

但是，保留第一级信息(品牌)是否有可能进入第二级(车型)呢?

我可以通过下面的查询得到2级信息。但我正在失去品牌名称。

SELECT ID, FATHER_ID, NAME FROM LISTS WHERE FATHER_ID IN (SELECT ID FROM LISTS WHERE FATHER_ID IN (SELECT ID FROM LISTS WHERE NAME = 'Car'))

提前谢谢你

对

Answer 1

遍历树，构建一个累积字符串，然后过滤结果，只保留最深层的

With FullTree as (
Select ID, '' as Names, 0 as Level
From Lists
Where Father_ID = 0
Union All
Select ID, f.Names + ' | ' + c.Name as Names, f.level+1 as Level
From Lists c inner join FullTree f on c.Father_ID = f.ID
)
Select Names from FullTree 
Where Level = (Select max(Level) From FullTree)
Order by Names

如果您愿意，您可以修剪字符串的左侧部分，以删除初始竖条。

Answer 2

你可以从树的最底层开始反向走。

WITH cte AS (
SELECT l.ID, l.FATHER_ID, l.NAME
FROM LISTS l
WHERE NOT EXISTS (SELECT 1
FROM LISTS child
WHERE child.FATHER_ID = l.ID)
UNION ALL
SELECT parent.ID, parent.FATHER_ID, CONCAT(parent.NAME, '|', child.NAME)
FROM LISTS parent
JOIN cte child ON child.FATHER_ID = l.ID
)
SELECT l.NAME
FROM cte
WHERE cte.FATHER_ID IS NULL;   -- this line removes intermediate levels