如何在SQL中查找成员记录完全重复的Group_ID

原始数据表 #

假设数据表名为group_members，数据如下：

需求说明 #

需要找出成员记录完全相同的Group_ID，即多个分组拥有完全一致的Group_Member_ID集合。比如A01、C01、E01都包含J01、K01、L01三个成员，这三个分组就是目标结果。

解决方案 #

核心思路是给每个分组生成唯一的「成员特征串」（将成员ID排序后拼接），再筛选出特征串重复的分组：

通用SQL（适用于PostgreSQL、SQL Server等支持STRING_AGG的数据库） #

WITH group_member_signatures AS (
    SELECT 
        Group_ID,
        -- 按成员ID排序后拼接，确保相同成员集合的特征串一致
        STRING_AGG(Group_Member_ID, ',' ORDER BY Group_Member_ID) AS member_signature
    FROM group_members
    GROUP BY Group_ID
)
SELECT gm.Group_ID
FROM group_members gm
JOIN group_member_signatures gms ON gm.Group_ID = gms.Group_ID
WHERE gms.member_signature IN (
    SELECT member_signature
    FROM group_member_signatures
    GROUP BY member_signature
    HAVING COUNT(*) > 1
)
GROUP BY gm.Group_ID
ORDER BY gm.Group_ID;

MySQL适配版本 #

将STRING_AGG替换为GROUP_CONCAT：

WITH group_member_signatures AS (
    SELECT 
        Group_ID,
        GROUP_CONCAT(Group_Member_ID ORDER BY Group_Member_ID SEPARATOR ',') AS member_signature
    FROM group_members
    GROUP BY Group_ID
)
SELECT gm.Group_ID
FROM group_members gm
JOIN group_member_signatures gms ON gm.Group_ID = gms.Group_ID
WHERE gms.member_signature IN (
    SELECT member_signature
    FROM group_member_signatures
    GROUP BY member_signature
    HAVING COUNT(*) > 1
)
GROUP BY gm.Group_ID
ORDER BY gm.Group_ID;

结果验证 #

执行上述SQL后，会返回预期结果：

Group_ID
A01
C01
E01

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