MYSQL 按名字分组查询最好成绩

员工可以多次答题，取最好成绩，按照答题分数，答题时长做排名。

SELECT b.USERNAME,b.SCORE,b.ATIME,b.CORRECT,b.ERROR,u.NAME,t.TITLE FROM (SELECT a.USERNAME,a.SCORE,a.ATIME,a.CORRECT,a.ERROR,a.TESTPAPER_ID FROM EXAM_ACHIEVEMENT a WHERE TESTPAPER_ID='222' AND a.SCORE HAVING 1 ORDER BY a.SCORE DESC) b left join EXAM_TESTPAPER t on b.TESTPAPER_ID = t.TESTPAPER_ID left join SYS_USER u on b.USERNAME = u.USERNAME GROUP BY b.USERNAME ORDER BY b.SCORE+0 DESC,b.ATIME+0 ASC

需要注意的地方：主表要显示的字段，一定要在子查询里面先查询出来；HAVING 1不能少；联合查询的位置；后面的排序字段，如果是varchar，要转换成整型。