MySQL : Get latest SCORE for each users where quiz_id = something

Question

这是我的表格:

id     |     quiz_id     |     user_id     |     date_replied     |     score

1             1                  1           2014-06-04 10:11:47         50
2             1                  1           2014-06-04 20:52:53         90
3             1                  5           2014-06-05 14:22:59         80
4             1                  5           2014-06-06 18:23:41         70

我想要的是获取每个 user_id 的最新 score，例如 quiz_id 为 1。

因此，quiz_id = 1；它会返回:

id     |     quiz_id     |     user_id     |     date_replied     |     score

2             1                  1           2014-06-04 20:52:53         90
4             1                  5           2014-06-06 18:23:41         70

有没有办法在 MySQL 中做到这一点？

谢谢!

Answer 1

select t1.* 
from your_table t1
join
(
  select user_id, max(date_replied) as mdate
  from your_table
  where quiz_id = 1
  group by user_id
) t2 on t1.user_id = t2.user_id and t1.date_replied = t2.mdate