Mysql - 查找仅由两个用户举行的对话
我有一个名为参与者的表，其中包含以下字段:
I have a table called participants with the following fields:
- id
- user_id
- conversation_id
背景是有至少两个或更多参与者的对话.我想找到一个只有两个指定用户进行的对话，对话中没有其他用户.
The background is that there are conversations with at least two or more participants. I want to find a conversation that has only been held by two specified users, with no other users in the conversation.
我想出了以下几点:
SELECT *
FROM participants
WHERE user_id = 1 OR user_id = 2
GROUP BY conversation_id
HAVING COUNT(*) = 1
鉴于此内容
您可以看到用户 1 和用户 2 与用户 3 共享一个会话(会话 1)，但也有单独的一个会话(会话 2).
you can see that the users 1 and 2 share a conversation with user 3 (conversation 1), but also have one conversation alone (conversation 2).
上面的查询实际上返回了正确的对话 ID(即 2) - 但我不确定查询是否正确.当我说 HAVING COUNT(*) = 2 它返回对话 1，我不知道为什么.直觉上，我使用了计数 - 如果设置为 1，它似乎可以工作 - 但我不确定它在这种情况下的作用.
The query above in fact returns the right conversation_id (i.e. 2) - but I am not sure whether the query is correct. When I say HAVING COUNT(*) = 2it returns conversation 1, and I am not sure why. Intuitively I used having count - and it seems to work if set to 1 - but I am not sure what it does in this context.
查询是否正确?如果是这样，为什么?如果没有，我需要更改什么才能使其正常工作?
Is the query correct? If so, why? If not, what do I need to change to make it work?
推荐答案
使用您的查询将不起作用，因为 where 子句过滤掉了 user_ids.使用
Using your query will not work since the where clause filters out the user_ids. Use
SELECT * FROM participants
GROUP BY conversation_id
HAVING sum(user_id not in (1,2)) = 0
user_id not in (1,2) 返回 1 如果 1,2 以外的 user_id 是在对话中，否则 0.所以使用 SUM 你可以把所有的情况加起来.如果没有找到，则总和为 0.
user_id not in (1,2) returns 1 if a user_id other than 1,2 are in a conversation and 0 otherwise. So using SUM you can add up all that cases. If none are found then the sum is 0.
相关文章