mysql 读取某个分组最大值，或最新记录的sql

第一种方法：

SELECT * FROM mysqltest a

WHERE 1 >(SELECT count(*) FROM mysqltest WHERE class = a.class and score>a.score)

ORDER BY a.class,a.score DESC;

这种还可以同个分组取多条

第二种方法：

select SQL_NO_CACHE puaction_memberid,puaction_id from push_action as a,(select SUBSTRING_INDEX(group_concat(puaction_id order by puaction_id desc),',',1) as pid from push_action where puaction_memberid IN ( 1396293,1394996,1394888,1394243,826977) group by puaction_memberid ) as b where a.puaction_id=b.pid

利用group_concat与USBSTRING_INDEX这两个mysql函数

group_concat是把每个分组里的某个字段的值用符号链接起来，并这个这段是按某种排序排列的，比如：1,2,3,4

USBSTRING_INDEX：取出以符号作为分割的字符串的第几个值，比如我们想获得上一个例子的2这个值：SUBSTRING_INDEX(字符串,',',2);

第三种方法

select puaction_memberid,puaction_id from push_action as a,(select max(puaction_id) as pid from push_action where puaction_memberid IN ( 1396293,1394996,1394888,1394243,826977) group by puaction_memberid limit 5) as b where a.puaction_id=b.pid

先取出每个分组的最大值。再进行链接查询。