mysql - sql消息表查询

Question

这是我的对话表:

conversationID  ||  userID
      1         ||    1
      1         ||    2
      2         ||    1
      2         ||    2
      2         ||    3

如您所见，每个对话可以包含 2 个或更多用户。

我正在尝试获取只有 2 个用户的对话的 ID。即仅包含用户 1 和 2 的对话，答案是对话 1。

但我如何获得它呢？

Answer 1

这将选择所有包含用户 1 或用户 2 或两者的对话，但没有其他人:

select conversationID
from conversations
group by conversationID
having count(*) = count(case when userID in (1,2) then 1 end)

如果您还希望所有对话都恰好有用户 1 和 2，而没有其他人，您还必须添加一个 and 条件:

select conversationID
from conversations
group by conversationID
having count(*) = count(case when userID in (1,2) then 1 end)
       and count(*) = 2 -- number of elements in set

如果userID可以重复，最好还是用distinct:

select conversationID
from conversations
group by conversationID
having
  count(distinct userID) = count(distinct case when userID in (1,2) then userID end)
  and count(distinct userID) = 2 -- number of elements in set