mysql - 从具有指定成员数的表中选择组 ID

Question

我的 members 表中有以下示例数据:

+----+----------+-----------+  
| id | users_id | groups_id |  
+----+----------+-----------+  
|  1 |        1 |         1 |  
|  2 |        2 |         1 |  
|  3 |        1 |         2 |  
|  4 |        2 |         2 |  
|  5 |        3 |         2 |  
+----+----------+-----------+  

我想选择包含两个指定成员的 groups_id，例如 1 和 2。我尝试使用此查询:

SELECT groups_id 
FROM   members 
WHERE  users_id IN ( 2, 1 ) 
GROUP  BY groups_id 
HAVING Count(users_id) = 2; 

但它仍然返回 groups_id 1 和 2。
感谢您的宝贵时间:)

Answer 1

如果users_id是一个int，你可以这样做:

SELECT groups_id 
FROM   members 
GROUP  BY groups_id 
HAVING MIN(users_id) = 1 AND MAX(users_id) = 2;

这将选择恰好具有两个相关 user_id 值 1 和 2 的 groups_id 值。

Demo here

编辑:

对于任意两个 user_id 值，您可以使用以下查询:

SELECT groups_id 
FROM   members 
GROUP  BY groups_id 
HAVING COUNT(DISTINCT CASE WHEN users_id IN (1, 2) THEN users_id END) = 2
       AND
       COUNT(CASE WHEN users_id NOT IN (1, 2) THEN users_id END) = 0