我有两个叫做会话的表和用户,他们与数据透视表conversation_user绑定,其中包含user_id和conversation_id列.
@H_301_6@| 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：
@H_301_6@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
对于1和2：
@H_301_6@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