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.

我怎么写这样的查询？

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

（编辑：济南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!