mysql的左关联

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

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

进入正题。

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

先看三张表：

users 表
id name

videos 表
id name

watch_records 表
id user_id video_id created_at

假如现在有这样几条数据

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
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」。