查询指定分类ID的所有子分类SQL语句问题排查

我将分类数据存储在单个categories表中，子分类层级无限制。需求是根据指定分类ID获取其所有关联子分类，用于新增或更新分类时维护path字段。

表结构与数据 #

id  parentId    name    path                         
A1   null       Cat 1   Cat 1                   
A2   A1         Cat 2   Cat 1 > Cat 2           
A3   A2         Cat 3   Cat 1 > Cat 2 > Cat 3   
A4   null       Cat A   Cat A                   
A5   A4         Cat B   Cat A > Cat B           

期望结果 #

• 当指定ID为A1时，返回所有层级的子分类：

[
    {
        "id": "A2",
        "parentId": "A1",
        "name": "Cat 2",
        "path": "Cat 1 > Cat 2"
    },
    {
        "id": "A3",
        "parentId": "A2",
        "name": "Cat 3",
        "path": "Cat 1 > Cat 2 > Cat 3"
    }
]

• 当指定ID为A2时，返回：

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

尝试的查询语句 #

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

问题排查与修正 #

原查询结果不符合预期的核心原因是CTE中未包含path字段，而你期望的返回结果需要这个字段。另外，原查询硬编码了目标IDA1，实际使用时建议改为参数化传入。

修正后的递归CTE查询如下：

with recursive cte (id, parentId, name, path) AS (
    -- 初始步骤：获取目标分类的直接子节点
    select
        id,
        parentId,
        name,
        path
    from
        categories
    where
        parentId = 'A1' -- 替换为你需要查询的分类ID，比如'A2'
    union all
    -- 递归步骤：遍历所有层级的子节点
    select
        c.id,
        c.parentId,
        c.name,
        c.path
    from
        categories c
    inner join cte on c.parentId = cte.id
)
select id, parentId, name, path from cte;

说明 #

1. 修正了CTE的字段定义，加入path字段，确保最终结果包含该字段；
2. 递归逻辑保持正确：初始查询获取目标ID的直接子节点，递归查询通过关联CTE获取所有子节点的子节点，实现无限层级的子分类遍历；
3. 如果需要动态传入分类ID，可根据数据库类型使用参数（如PostgreSQL用$1，MySQL用?），避免硬编码。

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