mysql – 从数据透视表中查找一行

我有两个叫做会话的表和用户,他们与数据透视表conversation_user绑定,其中包含user_id和conversation_id列.

| conversation_id | user_id |
|-----------------|---------|
|               1 |       1 |
|               1 |       2 |
|               2 |       1 |
|               2 |       2 |
|               2 |       3 |

我想向数据库发送一些用户ID并检索它们在一起的对话.

例如：

当我发送1和2时,我必须收到conversation_id 1,当我发送1,2和3时,我必须收到conversation_id 2.

我怎么写这样的查询？

最佳答案
您可以使用GROUP BY和HAVING：

SELECT conversation_id 
FROM tab                     -- subquery/temp table/whatever
GROUP BY conversation_id 
HAVING SUM(user_id = 1) > 0
   AND SUM(user_id = 2) > 0
   AND SUM(user_id = 3) > 0
   AND SUM(user_id NOT IN (1,2,3)) = 0

SqlFiddleDemo

对于1和2：

SELECT conversation_id 
FROM tab
GROUP BY conversation_id 
HAVING SUM(user_id = 1) > 0
   AND SUM(user_id = 2) > 0
   AND SUM(user_id NOT IN (1,2)) = 0