【mysql】分组查询每组的最新一条数据

1.原始数据
学生成绩表

2.想要获取每个考生最新的考试成绩，网上的例子

SELECT a.* FROM (SELECT * FROM scoreinfo ORDER BY scoreinfo.CreateTime Desc ) as a
GROUP BY a.SNum
ORDER BY a.CreateTime;

执行结果为

很明显执行结果不对，通过网上查找发现

mysql5.7时，子查询的排序已经变为无效了

想要使排序生效，必须执行 limit 条数限制

SELECT a.* FROM (SELECT * FROM scoreinfo ORDER BY scoreinfo.CreateTime Desc LIMIT 1000) as a
GROUP BY a.SNum
ORDER BY a.CreateTime;

执行结果为想要的数据

方法2

SELECT * FROM scoreinfo
JOIN (SELECT SNum, MAX(CreateTime) AS CreateTime FROM scoreinfo group by scoreinfo.SNum) AS A
WHERE scoreinfo.SNum=a.SNum&&scoreinfo.CreateTime=a.CreateTime;

执行结果

参考：https://www.cnblogs.com/huashengweilong/p/12005743.html