查询类似聊天会话列表的一个select语句

Question

SELECT
users.name,users.photo,history_message.payload,history_message.ms,
history_message.message_type, history_message.source,history_message.destination,
history_message.file_path
FROM users RIGHT JOIN history_message ON users.user_id = history_message.source
or users.user_id = history_message.destination
WHERE (history_message.destination = "sim001" and history_message.dst_status != '0' and users.user_id != 'sim001' )
or (history_message.source ="sim001" and history_message.src_status != '0' and users.user_id != 'sim001')
ORDER BY ms DESC;

这是现在我写的sql 但是达不到需求,查询结果是

我想将source字段和destination 这两个字段如果值是相互的,只保留一条ms为最大的
如果最好再加一个count代表未读的信息条数 dst_status = "3" 为未读

Answer 1

1. "值是相互的"是什么意思?相等还是不相等？或者说你的意思是对于同一组source和destination ,只保留最近一条记录？是否有方向要求？

2. 能不能给下这两张表的create table 语句及小部分测试数据？

目前能想到的就是开窗函数分组排序,取每个组里的第一条,但是不确定你分组的标准,A-B和B-A是两组还是一组？另外开窗函数外如果有聚合函数,那么开窗函数内的写法是有点区别的,而且不同的数据库这里的语法也有点区别,所以你最好说明一下数据库版本,并且给出建表sql,方便答题人进行调试

---

8.0支持开窗函数，所以重点就在你的分组了，根据你补充的描述来看，这个分组是不管顺序的，因此这里需要用到case when 来强制指定顺序，将双方拼成一个字符串，再使用这个字符串进行分组，sql如下，已经包含了你要的count了，count的分组方式和取最新一条的分组方式保持一致

select t.* from  (
SELECT
users.name,users.photo,history_message.payload,history_message.ms,
history_message.message_type, history_message.source,history_message.destination,
history_message.file_path,
count(case when dst_status =3 then 1 end ) over  (partition by
case when history_message.source='sim001' 
then concat(history_message.source,',',history_message.destination)
else concat(history_message.destination,',',history_message.source)
end ) ct,
row_number()over( partition by
case when history_message.source='sim001' 
then concat(history_message.source,',',history_message.destination)
else concat(history_message.destination,',',history_message.source)
end order by ms desc) rn
FROM users RIGHT JOIN history_message ON users.user_id = history_message.source
or users.user_id = history_message.destination
WHERE (history_message.destination = "sim001" and history_message.dst_status != '0' and users.user_id != 'sim001' )
or (history_message.source ="sim001" and history_message.src_status != '0' and users.user_id != 'sim001')
) t
 where rn=1