可能是 laravel 中的 eloquent 模型查询用的实在太爽,感觉自己都不太会写 sql 语句了😅😅。现在回想自己的 sql 技术的顶峰应该就是大学的课堂了,老师带着大家建表,学生表、班级表、成绩表想想还有点记忆尤新,那时候数据库连接都是直接终端敲命令的,机房的数据库密码统一是 wodexinmima
,转眼四年有余了。
现在的框架确实封装程度太高,大大降低了各种技术的门槛,大大提高了代码的维护性,否则一个十几乃至几十行的 sql 语句,谁能看得懂,但是又从反面暴露一件事,sql 的基础查询都快丢了,吓得我立马温习了一遍 mysql 查询。
进入正题。
最近有这么一个业务出现了 bug,用户在观看视频的时候会生成一条观看记录,观看记录的顺序始终是最近观看的视频放在最前面,但是如果用户看过视频两次,第二次看的记录并没有显示在最前面,反而还是以第一次观看的时间为准。
先看三张表:
1 | users 表 |
假如现在有这样几条数据
users 表
id | name |
---|---|
1 | Jake |
videos 表
id | name |
---|---|
11 | 面试技巧 |
22 | 快消行业介绍 |
33 | 职场礼仪 |
watch_records 表
id | user_id | video_id | created_at |
---|---|---|---|
111 | 1 | 11 | 2019-07-01 05:31:59 |
222 | 1 | 22 | 2019-07-02 05:31:59 |
333 | 1 | 33 | 2019-07-03 05:31:59 |
444 | 1 | 11 | 2019-07-04 05:31:59 |
现在要求查出的 watch_records 数据应该是 444 > 333 > 222 ,id 11 的视频因为最近再次观看取 444 这条记录而非 111 这一条。之前的 bug 是因为我直接 groupBy('video_id')
,从而得到的结果为 333 > 222 > 111。这个问题我研究了很长时间在我请教了一位朋友之后他说可以尝试「分组排序取前n」,这个关键字确实得到了很多答案,我试了几个查询没有得到合理答案,他直接给了我一段 sql :
1 | SELECT m1.* |
没有想到确实就查出了结果。
这段 sql 的点睛之笔在于 m2.id is NULL
,两张表左关联,右边表的创建时间比左边的大,只有左边是最大的时候,右边才会没有对应的记录,那也就是 m2.id is NULL
,巧妙利用了左关联,就做到了 「分组排序取前1」。