按MySQL查询返回特定结果的参数
问题描述:
在用户开始私人聊天之前(在两个成员之间,而不是群聊)之前,我想检查是否已经有一个只包含这两个成员的聊天。如果他们已经在他们的最后删除了聊天,当他们再次向同一用户发送消息时,我希望它与旧聊天合并,而不是为相同的两个成员启动重复聊天。按MySQL查询返回特定结果的参数
这是我的结构
`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
包含第3个成员。
原始SQL可能吗?我宁愿不要在php中循环,因为它需要很长时间的聊天。
答
这里有两种不同的方法得到的结果您正在寻找:
-- 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
更改表名,以适应您的实际建立。
答
使用条件COUNT
3210
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
+0
哎呀!它按原样工作! – Dave
+0
@Dave告诉你:) –
不知道为什么我需要为新创建的聊天添加左连接。但可能可以删除。 –
@JuanCarlosOropeza事实上,所要求的所有信息都只出现在一张表中,但情况并非总是如此,因此常常需要左连接:) – jpw