SQL Server递归查询：获取节点的1至5级子节点并横向展示

假设你的表名为workitems，包含workitem_id（工作项唯一ID）和parent_workitem_id（父工作项ID，根节点该字段为NULL）。以下提供两种常见场景的解决方案：

场景1：每个父节点一行，展示所有层级子节点 #

适合需要按父节点汇总下各级子节点的场景，同一层级多个子节点用逗号拼接（可根据需求调整为取单个节点）。

WITH recursive_hierarchy AS (
    -- 锚点：获取所有直接子节点（层级1）
    SELECT
        parent_workitem_id AS parent_id,
        workitem_id AS child_id,
        1 AS level
    FROM workitems
    WHERE parent_workitem_id IS NOT NULL
    UNION ALL
    -- 递归：逐级获取下一级子节点，最多到第5层
    SELECT
        r.parent_id,
        w.workitem_id AS child_id,
        r.level + 1 AS level
    FROM recursive_hierarchy r
    JOIN workitems w ON r.child_id = w.parent_workitem_id
    WHERE r.level < 5
)
-- 关联原表确保所有工作项都被展示，无子女节点显示NULL
SELECT
    COALESCE(r.parent_id, w.workitem_id) AS workitem,
    -- 按层级拼接子节点，不同数据库替换对应函数：MySQL用GROUP_CONCAT，Oracle用LISTAGG
    STRING_AGG(CASE WHEN r.level = 1 THEN r.child_id END, ', ') AS lvl1_child,
    STRING_AGG(CASE WHEN r.level = 2 THEN r.child_id END, ', ') AS lvl2_child,
    STRING_AGG(CASE WHEN r.level = 3 THEN r.child_id END, ', ') AS lvl3_child,
    STRING_AGG(CASE WHEN r.level = 4 THEN r.child_id END, ', ') AS lvl4_child,
    STRING_AGG(CASE WHEN r.level = 5 THEN r.child_id END, ', ') AS lvl5_child
FROM workitems w
LEFT JOIN recursive_hierarchy r ON w.workitem_id = r.parent_id
GROUP BY COALESCE(r.parent_id, w.workitem_id)
ORDER BY workitem;

场景2：每个完整层级路径一行 #

适合需要展示从根节点到各层级节点完整路径的场景，每条路径对应一行。

WITH recursive_paths AS (
    -- 锚点：根节点作为起始路径（层级1）
    SELECT
        workitem_id AS workitem,
        workitem_id AS lvl1_child,
        CAST(NULL AS INT) AS lvl2_child,
        CAST(NULL AS INT) AS lvl3_child,
        CAST(NULL AS INT) AS lvl4_child,
        CAST(NULL AS INT) AS lvl5_child,
        1 AS current_level
    FROM workitems
    WHERE parent_workitem_id IS NULL
    UNION ALL
    -- 递归：填充路径的下一层级节点
    SELECT
        r.workitem,
        r.lvl1_child,
        CASE WHEN r.current_level = 1 THEN w.workitem_id ELSE r.lvl2_child END,
        CASE WHEN r.current_level = 2 THEN w.workitem_id ELSE r.lvl3_child END,
        CASE WHEN r.current_level = 3 THEN w.workitem_id ELSE r.lvl4_child END,
        CASE WHEN r.current_level = 4 THEN w.workitem_id ELSE r.lvl5_child END,
        r.current_level + 1 AS current_level
    FROM recursive_paths r
    JOIN workitems w ON
        CASE r.current_level
            WHEN 1 THEN r.lvl1_child = w.parent_workitem_id
            WHEN 2 THEN r.lvl2_child = w.parent_workitem_id
            WHEN 3 THEN r.lvl3_child = w.parent_workitem_id
            WHEN 4 THEN r.lvl4_child = w.parent_workitem_id
        END
    WHERE r.current_level < 5
)
-- 去重并展示所有有效路径
SELECT DISTINCT
    workitem,
    lvl1_child,
    lvl2_child,
    lvl3_child,
    lvl4_child,
    lvl5_child
FROM recursive_paths
ORDER BY workitem, lvl1_child, lvl2_child;

注意事项 #

• 若根节点的parent_workitem_id不是NULL（比如用0标识），修改锚点的WHERE条件即可。
• 字段类型若不是INT，将CAST(NULL AS INT)替换为对应类型（如VARCHAR(50)）。
• 不同数据库的递归CTE语法和聚合函数略有差异，需根据使用的数据库调整（如MySQL 8.0+、PostgreSQL、SQL Server 2016+均支持递归CTE）。

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