基于group_concat实现树形节点的根/内部/叶子节点分组查询

正确SQL方案（MySQL 8.0+） #

WITH RECURSIVE node_hierarchy AS (
    -- 锚点成员：确定所有根节点（父节点为null的节点）
    SELECT 
        node_id AS current_node,
        node_id AS root_node
    FROM craig_test.test
    WHERE parent_node_id IS NULL

    UNION ALL

    -- 递归成员：遍历子节点，继承父节点的根节点
    SELECT 
        t.node_id AS current_node,
        nh.root_node
    FROM craig_test.test t
    JOIN node_hierarchy nh ON t.parent_node_id = nh.current_node
),
node_types AS (
    -- 标记每个节点的类型：根、内部、叶子
    SELECT 
        nh.root_node,
        nh.current_node,
        CASE
            WHEN t.parent_node_id IS NULL THEN 'root'
            WHEN EXISTS (SELECT 1 FROM craig_test.test t2 WHERE t2.parent_node_id = nh.current_node) THEN 'internal'
            ELSE 'leaf'
        END AS node_type
    FROM node_hierarchy nh
    JOIN craig_test.test t ON nh.current_node = t.node_id
)
-- 按根节点分组，聚合内部节点和叶子节点
SELECT 
    root_node AS root,
    GROUP_CONCAT(DISTINCT CASE WHEN node_type = 'internal' THEN current_node END ORDER BY current_node SEPARATOR ',') AS internal,
    GROUP_CONCAT(DISTINCT CASE WHEN node_type = 'leaf' THEN current_node END ORDER BY current_node SEPARATOR ',') AS leaf
FROM node_types
GROUP BY root_node;

方案说明 #

1. 递归CTE node_hierarchy：

   • 锚点部分先筛选出所有根节点，每个根节点的root_node字段指向自身。
   • 递归部分通过关联父节点与子节点，将子节点的根节点同步为父节点的根节点，完成整棵树的根节点映射。

2. CTE node_types：

   • 为每个节点标记类型：
     • root：父节点为null的节点
     • internal：存在子节点的非根节点（即该节点是其他节点的父节点）
     • leaf：没有子节点的节点

3. 最终聚合查询：

   • 按根节点分组，用GROUP_CONCAT分别聚合当前根节点下的内部节点和叶子节点，同时做去重与排序处理。

原代码问题分析 #

你之前的代码未遍历树形结构，仅通过单表关联无法建立子节点到根节点的层级映射，导致分组结果无法将子节点正确归属到对应的根节点下。递归CTE是解决树形结构根节点关联的核心手段。

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