查询类似聊天会话列表的一个select语句
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" 为未读
"值是相互的"是什么意思?相等还是不相等？或者说你的意思是对于同一组source和destination ,只保留最近一条记录？是否有方向要求？
能不能给下这两张表的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
/*
SQLyog Ultimate v11.27 (32 bit)
MySQL - 8.0.27
/
/!40101 SET NAMES utf8 */;
create table history_message (source varchar (96),destination varchar (96),message_type varchar (96),payload varchar (6144),ms bigint (20),src_status varchar (3),dst_status varchar (3),src_del_datetime datetime ,dst_del_datetime datetime ,file_path varchar (192)
);
insert into history_message (source, destination, message_type, payload, ms, src_status, dst_status, src_del_datetime, dst_del_datetime, file_path) values('sim001','sim002',NULL,'哪位!','1234567866123','2','2',NULL,NULL,NULL);
insert into history_message (source, destination, message_type, payload, ms, src_status, dst_status, src_del_datetime, dst_del_datetime, file_path) values('sim001','sim002',NULL,'你好!','1234567891234','2','2',NULL,NULL,NULL);
insert into history_message (source, destination, message_type, payload, ms, src_status, dst_status, src_del_datetime, dst_del_datetime, file_path) values('sim001','sim002',NULL,'hello!','165648661213464','2','2',NULL,NULL,NULL);
insert into history_message (source, destination, message_type, payload, ms, src_status, dst_status, src_del_datetime, dst_del_datetime, file_path) values('sim001','sim003',NULL,NULL,'1234567891236','2','2',NULL,NULL,NULL);
insert into history_message (source, destination, message_type, payload, ms, src_status, dst_status, src_del_datetime, dst_del_datetime, file_path) values('sim001','sim004',NULL,NULL,'1234567891237','0','2',NULL,NULL,NULL);
insert into history_message (source, destination, message_type, payload, ms, src_status, dst_status, src_del_datetime, dst_del_datetime, file_path) values('sim002','sim001',NULL,'这里','1234567891235','2','2',NULL,NULL,NULL);
insert into history_message (source, destination, message_type, payload, ms, src_status, dst_status, src_del_datetime, dst_del_datetime, file_path) values('sim002','sim004',NULL,'我是','161616513131','2','2',NULL,NULL,NULL);
PS：问答VIP年卡 【限时加赠：IT技术图书免费领】，了解详情>>> https://vip.csdn.net/askvip?utm_source=1146287632