我有两个叫做会话的表和用户,他们与数据透视表conversation_user绑定,其中包含user_id和conversation_id列.
| conversation_id | user_id |
|-----------------|---------|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
例如：
当我发送1和2时,我必须收到conversation_id 1,当我发送1,2和3时,我必须收到conversation_id 2.
我怎么写这样的查询？
最佳答案
您可以使用GROUP BY和HAVING：
原文链接：https://www.f2er.com/mysql/433867.htmlSELECT 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
对于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