如何编写递归查询获取每个ID的所有子ID？

现有树形结构表 tblName，结构如下：

需要得到每个ID对应的所有层级子ID列表，期望结果：

• 1: [2,3,4]
• 2: [3,4]
• 3: [4]
• 4: null

你之前的递归查询只能指定单个ID，无法批量处理所有节点，以下是修改后的解决方案：

PostgreSQL 实现方案 #

WITH RECURSIVE all_descendants AS (
    -- 初始层：关联每个节点与它的直接子节点
    SELECT 
        t.ID AS parent_id,
        t_child.ID AS child_id
    FROM "tblName" t
    LEFT JOIN "tblName" t_child ON t.ID = t_child.ParentID
    UNION ALL
    -- 递归层：继续遍历子节点的子节点，直到无更深层级
    SELECT 
        ad.parent_id,
        t.ID AS child_id
    FROM all_descendants ad
    JOIN "tblName" t ON ad.child_id = t.ParentID
)
-- 聚合每个父节点的所有子节点为数组
SELECT 
    parent_id AS ID,
    CASE 
        -- 当所有子节点为空时返回null，否则移除空值后生成数组
        WHEN array_agg(child_id) = ARRAY[NULL] THEN NULL
        ELSE array_remove(array_agg(child_id), NULL)
    END AS children
FROM all_descendants
GROUP BY parent_id
ORDER BY parent_id;

MySQL 8.0+ 实现方案 #

如果使用MySQL，可通过JSON_ARRAYAGG生成JSON格式的子节点列表：

WITH RECURSIVE all_descendants AS (
    SELECT 
        t.ID AS parent_id,
        t_child.ID AS child_id
    FROM tblName t
    LEFT JOIN tblName t_child ON t.ID = t_child.ParentID
    UNION ALL
    SELECT 
        ad.parent_id,
        t.ID AS child_id
    FROM all_descendants ad
    JOIN tblName t ON ad.child_id = t.ParentID
)
SELECT 
    parent_id AS ID,
    CASE 
        WHEN COUNT(child_id) = 0 THEN NULL
        ELSE JSON_ARRAYAGG(child_id)
    END AS children
FROM all_descendants
GROUP BY parent_id
ORDER BY parent_id;

逻辑说明 #

1. 递归CTE all_descendants 会遍历每个节点的所有后代：
   • 初始查询先获取每个节点的直接子节点
   • 递归查询不断将已找到的子节点作为父节点，继续向下查找层级
2. 最后通过聚合函数将每个父节点的所有子节点合并为数组/JSON数组，处理无后代的节点返回null，完全匹配需求。

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