mysql8.0子查询的order by无效_综合

最近一个需求:查询最近视频的历史观看记录.
课程表(t_course)如下:

     
     
      1
      2

id	name	price

视频表(t_video)如下:

     
     
      1

id	video_url	course_id

记录表(t_record)如下:用户在观看一个视频后
会将update_time更新一下

     
     
      1
      2

id	user_id	video_id	course_id	create_time	update_time

第一种错误sql:

select * from t_record where user_id = 1 
group by course_id
order by update_time desc

     
     
      1
      2
      3

错误原因:执行group by后,mysql默认取的是每个分组的第一条记录.
导致后面的排序无效.

     
     
      1
      2

第二种错误sql:

select temp.* from(select * from t_record where user_id = 1 order by update_time desc) as temp 
group by temp.course_id

     
     
      1
      2
      3
      4
      5

错误原因:先生成一个按时间排好序的子表,groupby会默认取各个分组的第一条数据,
看起来是行得通的,但是实际是无效的.
原因是mysql5.7之后会优化子查询,使得子查询里的order by无效.

     
     
      1
      2
      3

第三种不合理的sql:

select temp.* from(select * from t_record where user_id = 1 order by update_time desclimit n) as temp 
group by temp.course_id

     
     
      1
      2
      3
      4
      5
      6

不合理原因:加上limit n后,能查询出来结果,但是得提前知道子查询所有记录的数量n,
再用 limit n就可以实现这个功能,但是不太合理.

     
     
      1
      2

第四种个人认为比较合理的sql:

select * from t_record 
inner join 
(select course_id,max(update_time) from t_record where user_id = 1 group by course_id
) as temp on temp.course_id = t_record.course_id and temp.update_time = t_record.update_time
order by t_record.update_time desc

     
     
      1
      2
      3
      4
      5
      6
      7
      8

子查询先查询出每个课程的最近的观看视频的记录course_id和update_time,然后再联表查询出
该记录的完整信息,将这些完整信息的记录排个序,就得到了结果.