这是我的对话表:
conversationID || userID
1 || 1
1 || 2
2 || 1
2 || 2
2 || 3
如您所见,每个对话可以包含 2 个或更多用户。
我正在尝试获取只有 2 个用户的对话 ID。即仅包含用户 1 和 2 的对话,答案是对话 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 可以重复,最好使用不同的:
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
你应该使用 having 子句。假设 ( 对话 ID, 用户 ID ) 是 PK 或 AK,则查询为:
select conversationID
from your_Table
group by conversationID
having count( * ) = 2
已编辑 与 1,2 个用户对话连接,这是一种索引友好的方法,没有关联子查询,也没有逐行函数。
select t1 conversationID
from your_Table t1
inner join
( select distinct conversationID
from your_Table
where userId in (1, 2)
) t2
on t1.conversationID = t2.conversationID
group by t1.conversationID
having count( distinct t1.userId ) = 2
希望对您有所帮助,
select conversationID from conversation
group by ConversationID having count(distinct UserID)=2;
SQLfiddle 演示