MySQL:使用AND条件从组成成员中搜索组

Question

以下三个 SQL 表描述了谁属于哪个组。例如，Ann 属于 A/B 组，Ben 属于 B/C 组，Chris 属于 A/C 组。

现在，当我想搜索“Ann 和 Ben 都属于的组”时，我们应该如何编写 SELECT 查询？还是我们应该以不同的方式设计这些表？

(1) 用户

+-----------+------------------+
| member_id | member_name      |
+-----------+------------------+
|         1 | Ann              |
|         2 | Ben              |
|         3 | Chris            |
+-----------+------------------+

(2) 组

+-----------+------------------+
| group_id  | group_name       |
+-----------+------------------+
|         1 | Group A          |
|         2 | Group B          |
|         3 | Group C          |
+-----------+------------------+

(3) 用户组

+-----------+------------------+
| group_id  | user_id          |
+-----------+------------------+
|         1 |                1 |
|         2 |                1 |
|         2 |                2 |
|         3 |                2 |
|         1 |                3 |
|         3 |                3 |
+-----------+------------------+

Answer 1

SELECT group_id, group_name 
FROM groups
JOIN users_groups ON groups.group_id = users_groups.group_id
JOIN users ON users_groups.user_id = users.member_id
WHERE member_id = 1
INTERSECT 
SELECT group_id, group_name 
FROM groups
JOIN users_groups ON groups.group_id = users_groups.group_id
JOIN users ON users_groups.user_id = users.member_id
WHERE member_id = 2

这将给出 Ann 和 Ben 所在的所有组的组 ID 和名称。如果您想按名称而不是 ID 进行搜索，请将 member_id = # 子句替换为 member_name = 'name'。

编辑:我忘记了 MySQL 没有 INTERSECT。所以上面的内容适用于大多数 DBMS，但下面的内容应该返回 MySQL 所需的结果。

SELECT groups.group_id, groups.group_name 
FROM groups
JOIN users_groups ON groups.group_id = users_groups.group_id
JOIN users ON users_groups.user_id = users.member_id
WHERE member_id = 1
AND groups.group_id IN (SELECT groups.group_id
                        FROM groups
                        JOIN users_groups ON groups.group_id = users_groups.group_id
                        JOIN users ON users_groups.user_id = users.member_id
                        WHERE member_id = 2)