如何基于叶子节点转置层级表并将根节点置于首列？

现有名为categories的层级数据表，结构与数据如下：

需要将该表转换为每行对应一个叶子节点的结构（已知层级最多3层），且根节点需显示在首列，预期输出格式如下：

针对固定3层级的结构，提供两种实现方案：

方案1：递归CTE（适配层级扩展） #

递归CTE可遍历全层级结构，自动记录每个节点的根节点、中间节点信息，最后筛选出叶子节点：

WITH RECURSIVE category_hierarchy AS (
    -- 锚点：根节点（parent_id为null）
    SELECT 
        id AS root_id,
        name AS root_name,
        id AS current_id,
        name AS current_name,
        0 AS level,
        CAST(NULL AS INT) AS child_id_1,
        CAST(NULL AS VARCHAR) AS child_name_1,
        CAST(NULL AS INT) AS child_id_2,
        CAST(NULL AS VARCHAR) AS child_name_2
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归遍历子节点，填充对应层级字段
    SELECT 
        ch.root_id,
        ch.root_name,
        c.id AS current_id,
        c.name AS current_name,
        ch.level + 1 AS level,
        CASE WHEN ch.level + 1 = 1 THEN c.id ELSE ch.child_id_1 END AS child_id_1,
        CASE WHEN ch.level + 1 = 1 THEN c.name ELSE ch.child_name_1 END AS child_name_1,
        CASE WHEN ch.level + 1 = 2 THEN c.id ELSE ch.child_id_2 END AS child_id_2,
        CASE WHEN ch.level + 1 = 2 THEN c.name ELSE ch.child_name_2 END AS child_name_2
    FROM category_hierarchy ch
    JOIN categories c ON ch.current_id = c.parent_id
)
-- 筛选叶子节点（无下属子节点）并输出
SELECT 
    root_id,
    root_name,
    child_id_1,
    child_name_1,
    child_id_2,
    child_name_2
FROM category_hierarchy ch
WHERE NOT EXISTS (
    SELECT 1 FROM categories c WHERE c.parent_id = ch.current_id
)
ORDER BY root_id, child_id_1, child_id_2;

方案2：多层自连接（性能更优） #

针对固定3层级，直接通过3层自连接关联根节点、一级子节点、二级子节点，覆盖所有叶子节点场景：

-- 场景1：根 -> 一级子 -> 二级子（二级子为叶子）
SELECT 
    r.id AS root_id,
    r.name AS root_name,
    c1.id AS child_id_1,
    c1.name AS child_name_1,
    c2.id AS child_id_2,
    c2.name AS child_name_2
FROM categories r
LEFT JOIN categories c1 ON r.id = c1.parent_id
LEFT JOIN categories c2 ON c1.id = c2.parent_id
WHERE c2.id IS NOT NULL

UNION ALL

-- 场景2：根 -> 一级子（一级子为叶子，无二级子）
SELECT 
    r.id AS root_id,
    r.name AS root_name,
    c1.id AS child_id_1,
    c1.name AS child_name_1,
    NULL AS child_id_2,
    NULL AS child_name_2
FROM categories r
LEFT JOIN categories c1 ON r.id = c1.parent_id
WHERE c1.id IS NOT NULL 
  AND NOT EXISTS (SELECT 1 FROM categories c2 WHERE c2.parent_id = c1.id)

UNION ALL

-- 场景3：根节点本身是叶子（无任何子节点）
SELECT 
    id AS root_id,
    name AS root_name,
    NULL AS child_id_1,
    NULL AS child_name_1,
    NULL AS child_id_2,
    NULL AS child_name_2
FROM categories
WHERE parent_id IS NULL
  AND NOT EXISTS (SELECT 1 FROM categories c WHERE c.parent_id = id)

ORDER BY root_id, child_id_1, child_id_2;

• 递归CTE方案更灵活，后续层级扩展时只需调整递归逻辑即可；
• 多层自连接方案针对固定3层级设计，执行效率更高，适合大数据量场景；
• 结果按根节点ID、一级子节点ID、二级子节点ID排序，结构更清晰。

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