SQL递归查询所有分类所属顶层根主分类的实现方法

原有代码的问题 #

你写的初始递归框架有两个关键问题，无法正确传递根分类信息：

• 锚点查询没有单独设置字段存储归属主分类的信息，递归过程中没有固定载体往下传递根节点数据
• 递归成员的SELECT逻辑写错了：没有读取子分类自身的category_id、category_name，反而直接取了父节点的ID，不仅子分类信息完全丢失，也无法继续向下遍历更深层级的分类。

实现思路 #

要拿到每个分类对应的顶层主分类，不需要递归时每次回溯找根，只要从根节点出发，把根节点的信息沿着父子关系一路往下传给所有子节点即可：

1. 锚点阶段筛选所有super_category IS NULL的主分类作为递归起点，此时主分类自己的归属根分类就是它本身
2. 递归阶段每次用已经查出的节点，关联查找它的直接子分类，子分类直接继承父节点带下来的主分类信息即可，直到找不到新的子分类就终止递归。

可直接运行的SQL代码 #

WITH RECURSIVE category_hierarchy AS (
    -- 锚点：拉取所有顶层主分类作为递归起点
    SELECT
        cat.category_id,
        cat.category_name,
        cat.super_category,
        -- 主分类自身就是根节点，直接存自己的ID和名称作为归属主分类
        cat.category_id AS main_category_id,
        cat.category_name AS main_category_name
    FROM category cat
    WHERE cat.super_category IS NULL

    UNION ALL

    -- 递归：向下遍历所有子分类，继承父节点携带的主分类信息
    SELECT
        child.category_id,
        child.category_name,
        child.super_category,
        -- 直接拿父节点已经传下来的主分类ID和名称，不用重新往上溯源
        ch.main_category_id,
        ch.main_category_name
    FROM category_hierarchy ch
    JOIN category child
        ON child.super_category = ch.category_id
)
SELECT * FROM category_hierarchy;

返回结果说明 #

查询结果字段含义：

• category_id、category_name、super_category是分类表原生字段，对应当前分类自身的属性
• main_category_id、main_category_name就是每个分类归属的顶层主分类信息，不管分类在树结构的第几层，都能直接拿到对应的根节点数据，不需要额外做多表关联回溯层级。

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