Mysql:获取按字段分组的具有最大值的行

Question

假设我有一个像这样的表消息:

id  sender  receiver  content  created_at
1    100       101    Hi        2015-12-01 
2    100       101    Hello     2015-12-02  
3    100       101    World     2015-11-02
4    103       101    Like      2015-11-05
5    103       101    Do        2015-11-04
6    105       102   ..................

指定接收者后，我想获取每个用户的最新消息和消息数量，即对于用户 101，我想获取:

2    100       101    Hello     2015-12-02     3
4    103       101    Like      2015-11-05     2

是否可以通过一条语句来做到这一点，或者什么是最优雅的方式？

Answer 1

在子查询中聚合 101 的信息，然后将其连接回原始表:

select m.*, mm.maxca
from messages m join
     (select m2.receiver, max(m2.created_at) as maxca, count(*) as cnt
      from messages m2
      where m2.receiver = 101
     ) mm
     on m.receiver = mm.receiver and m.created_at = mm.created_at
where m.receiver = 101 ;