查找所有重复的组，其中重复表示包含相同成员的组

Question

我需要一个SQL查询来查找所有重复的组。重复，我的意思是具有相同成员的任何组。所以正式的问题可以写成：返回所有channel_ids集，其中给定集合中包含 m 个通道：通道 i 到 m 包含相同的成员。我有一个连接表groups_members，其中包含列group_id、user_id列以及一个用户表和一个组表。感谢您的任何帮助。

Answer 1

您需要使用 WHERE IN 子句 - 然后在子查询中使用 GROUP BY 和 HAVING 子句（您可以说 HAVING COUNT（*）> 1 来查找重复项）。

Answer 2

谢谢。我终于想通了。

select array_agg(DISTINCT a.channel_id) as channels, a.members from (select channel_id, array_agg(user_id) as members from users_channels group by channel_id) a JOIN (select channel_id, array_agg(user_id) as members from users_channels group by channel_id) b ON a.members = b.members AND a.channel_id <> b.channel_id group by a.members;

Answer 3

您可以使用完全联接并查看是否有任何user_id为 null，这意味着有一个用户在一个组中，而不是另一个组中

select gm1.id, gm2.id from group_members gm1
full join group_members gm2 on gm2.user_id = gm1.user_id
where gm1.id < gm2.id
group by gm1.id, gm2.id
having count(case when gm1.user_id is null 
  or gm2.user_id is null then 1 end) = 0

另一种使用 2 not exists 子句来确保不在另一个组的用户列表中的用户不存在的方法。

select g.* from groups g1
join groups g2 on g2.id < g1.id
where not exists (
    select 1 from group_members gm1
    left join group_members gm2 on gm1.user_id = gm2.user_id and gm2.id = g2.id
    where gm1.group_id = g1.id
    and gm2.user_id is null
) and not exists (
    select 1 from group_members gm1
    left join group_members gm2 on gm1.user_id = gm2.user_id and gm2.id = g1.id
    where gm1.group_id = g2.id
    and gm2.user_id is null 
)