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).

上面的查询实际上返回正确的conversation_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?

推荐答案

使用您的查询将无法正常工作，因为其中子句过滤掉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不在（1,2）返回 1 如果 user_id 而非 1,2 在会话中， 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屋！