保存用户考试信息的表 score
user_id | test_time | test_score
-----------------
1 | 2009.1.1 | 50
1 | 2009.1.2 | 60
2 | 2009.1.2 | 60
1 | 2009.1.5 | 80
2 | 2009.1.6 | 90
2 | 2009.1.8 | 70
group by
SELECT * FROM score GROUP BY user_id ORDER BY test_score DESC
但MYSQL是先GROUP后ORDER的,行不通。
max来搞定
SELECT *, MAX(test_score) s FROM score GROUP BY user_id ORDER BY s DESC
但这样test_time 就不对应了。
先ORDER后GROUP
SELECT tmp.* FROM (SELECT * FROM score ORDER BY test_score DESC) tmp GROUP BY tmp.user_id