关于sql查询某个用户最近的一条记录

1. 在mysql版本是5.7之前的可以这么写：
   select
   id as resultId,
   user_id,
   score,
   exam_id,
   start_time,
   create_time,
   end_time,
   user_name
   from (select * from question_paper_result
   where exam_id =#{examId}
   and is_delete = 1 and finish_status = 4
   ORDER BY create_time DESC) temp
   group by user_id ORDER BY score desc

2. 但是在版本5.7之后就这个就会有问题，发现并不会再根据时间排序后更具用户id来分组时，顺序也是会乱的。
   是因为mysql5.7的一个优化：Derived table实际上是一种特殊的subquery，它位于SQL语句中FROM子句里面，可以看做是一个单独的表。MySQL5.7之前的处理都是对Derived table进行Materialize，生成一个临时表保存Derived table的结果，然后利用临时表来协助完成其他父查询的操作，比如JOIN等操作。MySQL5.7中对Derived table做了一个新特性。该特性允许将符合条件的Derived table中的子表与父查询的表合并进行直接JOIN。

3. 5.7之后可以这样写：
   select
   id as resultId,
   user_id,
   score,
   exam_id,
   start_time,
   create_time,
   end_time,
   user_name
   from (select * from question_paper_result
   where exam_id =#{examId}
   and is_delete = 1 and finish_status = 4
   ORDER BY create_time DESC limit 999999) temp
   group by user_id ORDER BY score desc

4. 另外一种方式也行只是效率太慢：
   select
   id as resultId,
   user_id,
   score,
   exam_id,
   start_time,
   create_time,
   end_time,
   user_name
   from question_paper_result x
   where x.create_time =(select max(create_time) from question_paper_result y
   where x.user_id = y.user_id and y.exam_id = #{examId}
   and y.is_delete = 1 and y.finish_status =4 )
   and x.is_delete =1 and x.finish_status =4 and x.exam_id = #{examId}
   order by score desc