sql消息表查询 [英] sql messages table query

问题描述

这是我的对话表:

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

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

as you can see each conversation can contain 2 users or more.

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

I am trying to get the id of the conversation that only 2 users are there. ie conversation that contains only users 1 & 2, the answer is conversation 1.

但是我怎么得到它?

推荐答案

这将选择所有具有用户1或用户2或同时具有两个用户但没有其他用户的对话:

This will select all conversations that have users 1 or user 2, or both, but no one else:

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

如果您还希望所有会话具有完全相同的用户1和2，并且没有其他用户，则还必须添加and条件:

If you also want all conversations that have exactly user 1 and 2, and no one else, you also have to add an and condition:

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:

If userID can be duplicated, it's also better to use 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

这篇关于sql消息表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！