数据结构
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)
这样分组,并且取分组最新的数据,为了实现聊天记录的获取。
下面有两种方法:假设你的表叫t_biz_sign ,'最新列'字段叫create_time ,需要分组的字段叫foreign_id
SELECT sign.*
FROM t_biz_sign sign
WHERE NOT exists(SELECT 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;
假定你表名为message
select * from message where (select count(*) from message as m where m.sendId = message.sendId and m.postDate >= message.postDate ) < 2