如何编写SQL实现表自递归查询获取嵌套分组的全部成员

mytable为分组-成员从属关系表，grp字段存储分组名，member字段存储分组下的成员（成员既可以是终端用户，也可以是下级子分组），表内基础数据如下：

要查询指定分组下所有层级的成员（包含子分组、子分组下的所有终端用户），使用标准SQL的递归CTE（公共表表达式）即可实现，不需要手动写死多层自关联，支持任意深度的层级遍历，兼容MySQL 8.0+、PostgreSQL、SQL Server等支持SQL:1999标准的数据库。

基础实现语句 #

WITH RECURSIVE group_members AS (
    -- 锚定段：查询起始分组的直接成员，作为递归起点
    SELECT member
    FROM mytable
    WHERE grp = 'Group-D'
    UNION ALL
    -- 递归段：关联上一轮查到的子分组，向下遍历下一层成员
    SELECT t.member
    FROM mytable t
    INNER JOIN group_members gm 
        ON t.grp = gm.member
)
-- 去重后返回所有成员，可按需调整排序规则
SELECT DISTINCT member 
FROM group_members 
ORDER BY member;

注意：SQL Server 中无需添加RECURSIVE关键字，直接使用WITH group_members AS (...)语法即可。

逻辑说明 #

递归CTE的执行逻辑分为两步循环执行，直到没有新数据生成时自动终止：

1. 先执行锚定段语句，拿到目标分组的第一层直接成员，作为初始结果集
2. 反复执行递归段语句：每次拿上一轮结果集中的子分组值，和原表grp字段关联，查出对应子分组下的成员，追加到结果集中

执行上述语句后，会返回预期的全量成员：

• Group-A
• Group-B
• Group-C
• User-1
• User-2
• User-3
• User-4
• User-5

环形数据兼容写法 #

如果业务中可能出现环形从属（比如Group-A包含Group-B，Group-B又反向包含Group-A），可以额外增加路径校验逻辑，避免递归死循环：

WITH RECURSIVE group_members AS (
    SELECT 
        member,
        CAST(member AS CHAR(1000)) AS path -- 记录遍历路径
    FROM mytable
    WHERE grp = 'Group-D'
    UNION ALL
    SELECT 
        t.member,
        CONCAT(gm.path, '>', t.member)
    FROM mytable t
    INNER JOIN group_members gm 
        ON t.grp = gm.member
    -- 校验当前成员不在已遍历路径中，阻断循环
    WHERE FIND_IN_SET(t.member, REPLACE(gm.path, '>', ',')) = 0
)
SELECT DISTINCT member FROM group_members;

对于不支持递归CTE的旧版MySQL（5.x及以下版本），无法通过单条SELECT语句实现该需求，需要编写存储过程，通过循环遍历临时表的方式模拟递归逻辑，建议升级数据库版本使用标准递归语法。

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