mysql - 如何从属于两个组的多对多表中选择一个用户？

Question

我实际上并不需要以下查询，但我醒来时遇到了这个“理论问题”，我无法弄清楚。假设我有三个表:用户表、组表和多对多的 users_groups 表。因此，如果一个用户属于第 1 组和第 2 组，则每个用户都会有两个不同的行。

现在，假设有很多组，我该如何具体选择既属于组 2 又属于组 3 的用户？

我尝试了这些方法，但它显示为空:

SELECT * FROM `users_groups` GROUP BY user_id HAVING group_id = 2 AND group_id = 3

我想这假设两个组都在同一行，这显然行不通。我该怎么做？

编辑:两种变体如何:用户必须仅在这两个组中，并且用户必须至少在这两个组中？

Answer 1

SELECT *
FROM users_groups
GROUP BY user_id
WHERE group_id IN (2,3)
HAVING COUNT(1) = 2

这当然假设 {user_id, group_id} 是唯一的(并且那里没有其他列可以添加额外的行到计数中)。否则你可以明确地确保这一点:

SELECT *
FROM users_groups
GROUP BY user_id
WHERE group_id IN (2,3)
HAVING COUNT(DISTINCT group_id) = 2

---

只有这两组稍微复杂一些。您可以:

SELECT *
FROM users_groups g1
GROUP BY user_id
WHERE group_id IN (2,3)
AND NOT EXISTS
(
    SELECT 1
    FROM users_groups AS g2
    WHERE g2.user_id = g1.user_id
    AND group_id NOT IN (2,3)
)
HAVING COUNT(1) = 2

或者，

SELECT *
FROM users_groups g1
GROUP BY user_id
HAVING COUNT(1) = 2
AND SUM(CASE WHEN group_id IN (2,3) THEN 1 ELSE 0 END) = 2

---

在第 2 组和第 3 组中，总计超过 2 组:

SELECT *
FROM users_groups g1
GROUP BY user_id
HAVING SUM(CASE WHEN group_id IN (2,3) THEN 1 ELSE 0 END) = 2
AND COUNT(1) > 2