按MySQL查询分组，返回带有参数的特定结果

Question

在用户开始私人聊天(在两个成员之间，而不是一个组聊天)之前，我想检查一下是否已经有一个只有这两个成员组成的聊天。如果他们删除了终端的聊天，当他们再次向同一个用户发送消息时，我希望它与旧的聊天合并，而不是为相同的两个成员启动重复的聊天。

这是我的结构

`chats` table
id     created_time 
 1     [TIMESTAMP]
 2     [TIMESTAMP]

`chats.parties` table   
id     chat_id     member_id     invited_by
 1     1           1             0 // creator of chat
 2     1           2             1
 3     1           3             1
 4     2           1             0
 5     2           2             1

按chat_id分组，但只返回包含member_id=1和member_id=2行的结果；不多，不少。

对于上面的表，只返回chat_id=2行，因为chat_id=1包含第三个成员。

如果使用原始SQL，这是可能的吗？我不喜欢在php中循环，因为这需要一段时间来进行大量的聊天。

Answer 1

这里有两种不同的方法来获得您想要的结果：

-- using conditional aggregation
select chat_id from chat_parties
group by chat_id
having sum(case when member_id = 1 then 1 else 0 end) > 0
   and sum(case when member_id = 2 then 1 else 0 end) > 0
   and sum(case when member_id not in (1, 2) then 1 else 0 end) = 0

-- using a correlated subquery
select chat_id from chat_parties c1
where member_id in (1,2)
and not exists (
  select 1 from chat_parties where chat_id = c1.chat_id and member_id not in (1,2)
)
group by chat_id having count(distinct member_id) = 2

更改表名以适应实际设置。

Answer 2

使用条件COUNT

SQL Fiddle演示

SELECT c.`id`
FROM chats c
LEFT JOIN chats_parties cp
       ON c.`id`= cp.`chat_id`
GROUP BY c.`id`
HAVING COUNT(case when `member_id` = 1 then 1 END) >= 1 
   AND COUNT(case when `member_id` = 2 then 1 END) >= 1 
   AND COUNT(DISTINCT `member_id` ) = 2