SQL单表递归查询或自连接实现多级层级分组数据关联查询咨询

核心技术术语 #

你要实现的树形结构层级遍历，在标准SQL中可以通过*递归公共表表达式（Recursive CTE）*实现，这也是目前主流的实现方案，支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等绝大多数主流数据库，比手写循环逻辑更简洁、执行效率更高。

实现思路 #

递归CTE由固定的两部分组成，通过UNION ALL拼接：

• 锚点成员：定义递归的起始节点，也就是你最初的查询逻辑，取父ID为1083的第一层子分组，同时可以标记当前层级、保留上层的字段值
• 递归成员：将CTE自身和CM_GROUP表关联，关联条件为子分组的pgrp_id等于上一层分组的grp_id，数据库会自动迭代执行直到没有符合条件的子节点为止

字段映射说明 #

你需要输出的system id、system name、site id、site name、state id、state name六个字段，需要和你的层级规则匹配，以下示例默认层级规则为：1083为根节点，第一层子节点为state、第二层为site、第三层为system，你可以根据实际业务调整层级和字段的对应关系。

示例代码 #

WITH RECURSIVE group_hierarchy AS (
    -- 锚点查询：获取根节点下第一层子分组（state层级）
    SELECT 
        grp_id AS state_id,
        name AS state_name,
        CAST(NULL AS UNSIGNED) AS site_id,
        CAST(NULL AS CHAR(255)) AS site_name,
        CAST(NULL AS UNSIGNED) AS system_id,
        CAST(NULL AS CHAR(255)) AS system_name,
        1 AS level,
        grp_id AS current_grp_id
    FROM CM_GROUP 
    WHERE pgrp_id = 1083

    UNION ALL

    -- 递归查询：逐层获取子分组
    SELECT
        h.state_id,
        h.state_name,
        CASE WHEN h.level + 1 = 2 THEN g.grp_id ELSE h.site_id END AS site_id,
        CASE WHEN h.level + 1 = 2 THEN g.name ELSE h.site_name END AS site_name,
        CASE WHEN h.level + 1 = 3 THEN g.grp_id ELSE h.system_id END AS system_id,
        CASE WHEN h.level + 1 = 3 THEN g.name ELSE h.system_name END AS system_name,
        h.level + 1 AS level,
        g.grp_id AS current_grp_id
    FROM group_hierarchy h
    INNER JOIN CM_GROUP g ON g.pgrp_id = h.current_grp_id
    -- 如需不限制递归深度，可删除下面的层级限制条件
    WHERE h.level < 3
)
-- 输出最终结果
SELECT system_id, system_name, site_id, site_name, state_id, state_name
FROM group_hierarchy
-- 如需只返回三级层级都齐全的完整关联数据，可打开下面的注释
-- WHERE level = 3

注意事项 #

• 如果你使用的是MySQL 5.x等不支持递归CTE的低版本数据库，可以通过自定义存储过程、自定义函数的方式实现层级遍历，性能略低于递归CTE
• 如果实际层级超过3层，调整递归深度限制和字段赋值的CASE WHEN规则即可
• 如果层级不固定，不需要输出按层级拆分的字段，只需要输出全量的父、子分组对应关系，可以简化递归逻辑，不需要处理字段赋值，只保留grp_id、pgrp_id、name、level字段即可

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