如何查询分类表中指定节点的上级层级数据？

数据表结构与示例数据 #

id  name    parentId    path    
A1  Cat 1   0           Cat 1
A2  Cat 2   A1          Cat 1 > Cat 2
A3  Cat 3   A2          Cat 1 > Cat 2 > Cat 3
A4  Cat 4   A3          Cat 1 > Cat 2 > Cat 3 > Cat 4
A5  Cat 5   A4          Cat 1 > Cat 2 > Cat 3 > Cat 4 > Cat 5
A6  Cat 6   A5          Cat 1 > Cat 2 > Cat 3 > Cat 4 > Cat 5 > Cat 6

需求与问题 #

需要获取指定节点（如A3）的所有上级层级数据直至根节点，但当前使用的递归CTE查询返回的是该节点的下层子节点，不符合预期。

当前错误查询语句 #

with recursive cte (id, name, parentId, path) AS (
    select
        id,
        name,
        parentId,
        path
    from
        categories
    where
        parentId = 'A3'
    union
    all
    select
        c.id,
        c.name,
        c.parentId,
        c.path
    from
        categories c
        inner join cte on c.parentId = cte.id
)
select
    *
from
    cte;

当前错误返回结果 #

id  name    parentId    path
A4  Cat 4   A3  Cat 1 > Cat 2 > Cat 3 > Cat 4
A5  Cat 5   A4  Cat 1 > Cat 2 > Cat 3 > Cat 4 > Cat 5
A6  Cat 6   A5  Cat 1 > Cat 2 > Cat 3 > Cat 4 > Cat 5 > Cat 6

预期结果 #

id  name    parentId    path
A2  Cat 2   A1          Cat 1 > Cat 2
A1  Cat 1   0           Cat 1

正确的SQL查询方案 #

要实现向上递归查询上级节点，需调整递归CTE的起始条件与关联逻辑，核心是从指定节点开始向上关联父节点，而非向下关联子节点：

WITH RECURSIVE cte AS (
    -- 起始步骤：先获取目标节点
    SELECT id, name, parentId, path
    FROM categories
    WHERE id = 'A3'
    UNION ALL
    -- 递归步骤：向上关联父节点，直到根节点
    SELECT c.id, c.name, c.parentId, c.path
    FROM categories c
    INNER JOIN cte ON c.id = cte.parentId
)
-- 过滤掉目标节点本身，只保留所有上级层级
SELECT * FROM cte WHERE id != 'A3';

如果不需要包含目标节点，也可以直接从目标节点的父节点作为起始点，简化逻辑：

WITH RECURSIVE cte AS (
    -- 起始步骤：直接获取目标节点的父节点
    SELECT id, name, parentId, path
    FROM categories
    WHERE id = (SELECT parentId FROM categories WHERE id = 'A3')
    UNION ALL
    -- 递归步骤：继续向上追溯父节点
    SELECT c.id, c.name, c.parentId, c.path
    FROM categories c
    INNER JOIN cte ON c.id = cte.parentId
)
SELECT * FROM cte;

以上两种写法均可得到预期的上级节点数据。

内容的提问来源于stack exchange，提问作者StormTrooper