如何在MySQL递归查询中添加子节点统计列？

修改后的完整查询语句 #

WITH RECURSIVE generation AS (
    SELECT id, name, parent_id, 0 AS generation_number 
    FROM users 
    WHERE parent_id IS NULL
    UNION ALL 
    SELECT 
        child.id,
        child.name, 
        child.parent_id,
        generation_number + 1 AS generation_number 
    FROM users child 
    JOIN generation g ON g.id = child.parent_id 
),
child_count AS (
    SELECT parent_id, COUNT(*) AS child_count
    FROM users
    GROUP BY parent_id
)
SELECT 
    g.id, 
    g.name, 
    COALESCE(g.parent_id, 0) AS parent_id,
    g.generation_number AS `generation/level`,
    COALESCE(cc.child_count, 0) AS `子节点数量`
FROM generation g
LEFT JOIN child_count cc ON g.id = cc.parent_id
ORDER BY g.id;

改动说明 #

• 新增子节点统计CTE：添加child_count公共表表达式，通过分组统计users表中每个parent_id对应的记录数，得到每个节点的直接子节点数量
• 关联层级与统计结果：在最终查询中，将原层级查询结果与子节点统计结果左连接，确保所有节点都能被匹配到
• 处理空值场景：用COALESCE函数将无子女节点的NULL值转换为0，符合示例结果的展示格式
• 统一根节点格式：把根节点的parent_id（原数据为NULL）替换为0，和你给出的示例结果保持一致

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