求助：递归获取指定分类全层级的SQL查询问题（含父级记录）

问题背景 #

分类数据存储在categories单表中，支持无限层级子节点，需要获取指定分类（id=2，名称为Cat 2）的所有关联层级数据，包含其所有父级、自身以及所有子级。

表结构 #

id  parentId    name    path                    isLastLevel     
1   0           Cat 1   Cat 1                   0
2   1           Cat 2   Cat 1 > Cat 2           0
3   2           Cat 3   Cat 1 > Cat 2 > Cat 3   1
4   0           Cat A   Cat A                   0
5   4           Cat B   Cat A > Cat B           1

期望结果 #

[
    {
        id: 1,
        parentId: 0,
        name: "Cat 1",
        path: "Cat 1",
        isLastLevel: 0
    },
    {
        id: 2,
        parentId: 1,
        name: "Cat 2",
        path: "Cat 1 > Cat 2",
        isLastLevel: 0
    },
    {
        id: 3,
        parentId: 2,
        name: "Cat 3",
        path: "Cat 1 > Cat 2 > Cat 3",
        isLastLevel: 1
    }
]

原查询问题 #

原递归CTE仅能获取子级，无法返回父级记录：

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

修正后的查询方案 #

需要分别递归向上查询所有父级（含自身）、向下查询所有子级（含自身），再合并结果去重：

WITH recursive parent_cte AS (
    -- 起始节点：指定分类本身
    SELECT id, parentId, name, path, isLastLevel
    FROM categories
    WHERE id = 2
    UNION ALL
    -- 向上递归查找父级
    SELECT c.id, c.parentId, c.name, c.path, c.isLastLevel
    FROM categories c
    INNER JOIN parent_cte pc ON c.id = pc.parentId
),
child_cte AS (
    -- 起始节点：指定分类本身
    SELECT id, parentId, name, path, isLastLevel
    FROM categories
    WHERE id = 2
    UNION ALL
    -- 向下递归查找子级
    SELECT c.id, c.parentId, c.name, c.path, c.isLastLevel
    FROM categories c
    INNER JOIN child_cte cc ON c.parentId = cc.id
)
-- 合并父级和子级结果，去重（因为起始节点会重复）
SELECT * FROM parent_cte
UNION
SELECT * FROM child_cte
ORDER BY id;

说明 #

1. parent_cte：从指定分类（id=2）开始，向上递归遍历所有父节点，直到根节点（parentId=0）
2. child_cte：从指定分类开始，向下递归遍历所有子节点，直到叶子节点（isLastLevel=1）
3. 使用UNION合并两个CTE的结果，自动去重重复的起始节点（id=2），最后按id排序得到完整层级数据

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