mysql的左关联

可能是 laravel 中的 eloquent 模型查询用的实在太爽,感觉自己都不太会写 sql 语句了😅😅。现在回想自己的 sql 技术的顶峰应该就是大学的课堂了,老师带着大家建表,学生表、班级表、成绩表想想还有点记忆尤新,那时候数据库连接都是直接终端敲命令的,机房的数据库密码统一是 wodexinmima,转眼四年有余了。

现在的框架确实封装程度太高,大大降低了各种技术的门槛,大大提高了代码的维护性,否则一个十几乃至几十行的 sql 语句,谁能看得懂,但是又从反面暴露一件事,sql 的基础查询都快丢了,吓得我立马温习了一遍 mysql 查询。

进入正题。

最近有这么一个业务出现了 bug,用户在观看视频的时候会生成一条观看记录,观看记录的顺序始终是最近观看的视频放在最前面,但是如果用户看过视频两次,第二次看的记录并没有显示在最前面,反而还是以第一次观看的时间为准。

先看三张表:

1
2
3
4
5
6
7
8
users 表
id name

videos 表
id name

watch_records 表
id user_id video_id created_at

假如现在有这样几条数据

users 表

idname
1Jake

videos 表

idname
11面试技巧
22快消行业介绍
33职场礼仪

watch_records 表

iduser_idvideo_idcreated_at
1111112019-07-01 05:31:59
2221222019-07-02 05:31:59
3331332019-07-03 05:31:59
4441112019-07-04 05:31:59

现在要求查出的 watch_records 数据应该是 444 > 333 > 222 ,id 11 的视频因为最近再次观看取 444 这条记录而非 111 这一条。之前的 bug 是因为我直接 groupBy('video_id') ,从而得到的结果为 333 > 222 > 111。这个问题我研究了很长时间在我请教了一位朋友之后他说可以尝试「分组排序取前n」,这个关键字确实得到了很多答案,我试了几个查询没有得到合理答案,他直接给了我一段 sql :

1
2
3
SELECT m1.*
FROM watch_records m1 LEFT JOIN watch_records m2
ON (m1.user_id = m2.user_id and m1.video_id=m2.video_id AND m1.created_at < m2.created_at) WHERE m2.id is NULL and m1.user_id=1 order by create_at desc;

没有想到确实就查出了结果。

这段 sql 的点睛之笔在于 m2.id is NULL ,两张表左关联,右边表的创建时间比左边的大,只有左边是最大的时候,右边才会没有对应的记录,那也就是 m2.id is NULL ,巧妙利用了左关联,就做到了 「分组排序取前1」。

坚持原创技术分享,您的支持将鼓励我继续创作!