最近一个需求:查询最近视频的历史观看记录.
课程表(t_course)如下:
id | name | price |
---|
视频表(t_video)如下:
id | video_url | course_id |
---|
记录表(t_record)如下:用户在观看一个视频后
会将update_time更新一下
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
错误原因:执行group by后,mysql默认取的是每个分组的第一条记录.
导致后面的排序无效.
第二种错误sql:
select temp.* from(
select * from t_record where user_id = 1
order by update_time desc
) as temp
group by temp.course_id
错误原因:先生成一个按时间排好序的子表,groupby会默认取各个分组的第一条数据,
看起来是行得通的,但是实际是无效的.
原因是mysql5.7之后会优化子查询,使得子查询里的order by无效.
第三种不合理的sql:
select temp.* from(
select * from t_record where user_id = 1
order by update_time desc
limit n
) as temp
group by temp.course_id
不合理原因:加上limit n后,能查询出来结果,但是得提前知道子查询所有记录的数量n,
再用 limit n就可以实现这个功能,但是不太合理.
第四种个人认为比较合理的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
子查询先查询出每个课程的最近的观看视频的记录course_id和update_time,然后再联表查询出
该记录的完整信息,将这些完整信息的记录排个序,就得到了结果.
但是这个sql有点问题,就是当有一节课的两个视频的update_time相等时,就会查询出
两节一样的课.当然啦,这种情况几乎不可能发生,发生了也没有什么大的影响.