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

Question

数据结构

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)
这样分组，并且取分组最新的数据，为了实现聊天记录的获取。

Answer 1

假定你表名为message

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


Answer 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;