Mysql - 查找仅由两个用户举行的对话 [英] Mysql - find conversation only being held by two users

问题描述

我有一个名为参与者的表，其中包含以下字段:

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.

这篇关于Mysql - 查找仅由两个用户举行的对话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！