mysql - 用于获取子节点所有父节点的递归 CTE 查询的 WHERE 语句

Question

我有以下数据集，它基本上是所有节点所属的树：

name         |id   |parent_id
Planet Earth |1    |0
North America|2    |1
Costa Rica   |46408|2
Alajuela     |46409|46408
Cartago      |46410|46408
Guanacaste   |46411|46408
Heredia      |46412|46408
...

我希望能够查询以获取树中的所有底部节点，以下查询非常适合此：

SELECT * 
FROM locations l 
WHERE l.id NOT IN (SELECT parent_id FROM locations)
AND l.name LIKE ?

我想以以下格式输出数据：

询问

SELECT * 
FROM locations l 
WHERE l.id NOT IN (SELECT parent_id FROM locations)
AND l.name LIKE 'he%'
SPOOKY SQL MAGIC GOES HERE...

输出

name   |id   |parent_id|parents
Heredia|46412|46408    |Planet Earth > North America > Costa Rica

我已经开始自己处理查询，如下所示：

WITH CTE AS (
    SELECT id, parent_id, name, CONVERT(VARCHAR(MAX),name) AS parents
        FROM locations
        WHERE idParent = 0
    UNION ALL
        SELECT l.id, l.parent_id, l.name,  c.parents + ' > ' + CONVERT(VARCHAR(MAX),t.name) AS parents
        FROM locations l
        INNER JOIN CTE c ON l.idParent = c.id
)
SELECT * FROM CTE

如您所见，我正在使用带有通用表表达式的递归，但我不知道如何组合这两个查询。上面的查询也在整个表上运行。

• 如何将上述 CTE 查询限制为表的子集？
• 如何实现我想要的输出？

标签： mysqlsqlcommon-table-expression