-
在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 -
但是在版本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。 -
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 -
另外一种方式也行只是效率太慢:
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
关于sql查询某个用户最近的一条记录
最新推荐文章于 2024-07-23 10:00:00 发布