mysql group by 如何解决聊天分组 取最新记录

数据结构

id sendId receiverId postDate
1 1 2 2015-04-06 12:12:01
2 1 3 2015-04-06 12:13:01
3 2 1 2015-04-06 12:14:01
4 2 3 2015-04-06 12:15:01
5 3 2 2015-04-06 12:16:01
6 4 1 2015-04-06 12:17:01
7 1 4 2015-04-06 12:18:01
8 2 4 2015-04-06 12:19:01

意思就是 按照( 1-2 ,2-1 )(1-3, 3-1) (1-4 ,4-1)( 2-4 ,4-2
这样分组,并且取分组最新的数据,为了实现聊天记录的获取。

假定你表名为message

select * from message where select count* from message as m where m.sendId = message.sendId and m.postDate >= message.postDate < 2

下面有两种方法:假设你的表叫t_biz_sign ,最新列字段叫create_time ,需要分组的字段叫foreign_id
SELECT sign.*
FROM t_biz_sign sign
WHERE NOT existsSELECT 1

 FROM t_biz_sign WHERE foreign_id = sign.foreign_id AND sign.create_time < create_time

ORDER BY create_time DESC;

SELECT *
FROM t_biz_sign sign
WHERE SELECT count*

 FROM t_biz_sign AS m WHERE m.foreign_id = sign.foreign_id AND m.create_time >= sign.create_time < 2;

该答案已被忽略,原因:

发表评论

电子邮件地址不会被公开。 必填项已用*标注