LeetCode 1341这道题是我刷SQL专题时遇到的一道很典型的“看似简单、实则坑多”的题目。名字叫“电影评分”很多人以为是写个AVG就完事结果卡在了“平局时返回字典序最小”这个规则上。这道题把多表连接、分组聚合、日期过滤、排序取Top 1这几个SQL高频考点全揉在了一起非常适合用来检验你对SQL执行顺序和边界条件的理解。我前前后后刷了三种写法踩过几个小坑今天把完整的拆解过程写下来给准备面试或者想巩固SQL基础的朋友做个参考。1. 先看懂题目三张表与两个问题1.1 表结构与实体关系原题给了三张表我用最直白的方式给大家还原一下。Movies表保存电影信息字段是movie_id电影主键和title电影标题。Users表保存用户信息字段是user_id用户主键和name用户名。MovieRating表是评分记录字段有movie_id、user_id、rating以及created_at而且这张表的主键是(movie_id, user_id)也就是说同一个用户对同一部电影只会有一条评分记录。这三张表的关系其实很清晰MovieRating是中间关联表movie_id引用Movies表user_id引用Users表。用数据库ER图的语言来说MovieRating和Movies是多对一关系MovieRating和Users也是多对一关系。换句话说一个用户可以评分多部电影一部电影可以被多个用户评分但用户和电影之间的评分关系只有一条。这里有个细节值得注意题目里说“评论电影数量最多”在MovieRating表里每一条记录就是一次评分也就是一次评论行为。因为表结构已经限制了同一用户对同一电影只能评一次所以只要统计每个用户在MovieRating表中出现的行数就能得到他评论过的电影数量。后面写SQL的时候直接COUNT(*)即可不需要去重。1.2 两个查询条件拆解题目要我们输出两个结果并且合并到一列results中。第一个问题是找到评论电影数量最多的用户名如果平局返回字典序最小的用户名。第二个问题是找到2020年2月平均评分最高的电影标题如果平局返回字典序最小的电影标题。第一眼看上去两个问题都可以用“分组聚合 排序 LIMIT 1”解决。第一个问题按用户分组统计数量按数量降序、用户名升序排取第一行。第二个问题按电影分组计算平均分按平均分降序、电影标题升序排取第一行。思路确实是这样但实际操作起来有几个地方容易犯迷糊。比如第二个问题很多人会忘记限定“2020年2月”这个时间窗口直接对所有时间的数据求平均分。还有一些人虽然记得过滤日期但写成了WHERE YEAR(created_at)2020 AND MONTH(created_at)2这样在数据量大的场景下可能会导致索引失效。后面第五章节我会专门聊这个坑。1.3 平局规则是这道题的真正考点如果只考“取最大”题目就太简单了所以它特意加了平局规则结果一样时按照字典序取最小的名字或标题。这个规则用ORDER BY很容易实现但很多人会漏掉或者排序字段方向写反。举个例子有两个用户A和B评论数量都是5条。我们想要的是两者之间名字靠前的那一个也就是按name ASC排列后的第一个。但如果只写ORDER BY COUNT(*) DESC数据库返回的顺序是不确定的可能返回A也可能返回B这就不符合题目要求了。正确的做法是ORDER BY COUNT(*) DESC, name ASC LIMIT 1。先按评论数降序排再按名字升序排取第一行这样平局时一定拿到字典序最小的那个。同理电影标题平局时也要按title ASC取第一个。这个排序规则是整个题目的灵魂理解了它后面的SQL就是一马平川。2. 从执行计划反推 SQL 写作顺序2.1 第一步先写“评论最多的用户”我们先不考虑合并结果分步实现。第一个查询的SQL可以这样写SELECT u.name AS results FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ORDER BY COUNT(*) DESC, u.name ASC LIMIT 1;这里我直接用了内连接JOIN因为题目要找的是“评论过电影的用户”没评论过的用户根本不可能成为最大评论者。GROUP BY后面为什么带u.name因为MySQL等多数数据库在ONLY_FULL_GROUP_BY模式下如果SELECT里出现了u.nameGROUP BY就必须包含它否则直接报错。虽然按u.user_id分组理论上u.name是唯一的但为了兼容性和可读性建议分组字段写全。ORDER BY COUNT(*) DESC是核心它统计每个用户的评论记录数然后从多到少排列。u.name ASC处理平局。LIMIT 1取第一个。执行顺序上数据库先做JOIN把Users和MovieRating关联起来然后GROUP BY分组对每组执行聚合计数接着ORDER BY排序最后LIMIT截取第一行。这个顺序想清楚就不会纠结为什么WHERE不能过滤聚合结果了。2.2 第二步写“2020年2月平均分最高的电影”第二个查询的SQL如下SELECT m.title AS results FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-02-01 AND mr.created_at 2020-03-01 GROUP BY m.movie_id, m.title ORDER BY AVG(mr.rating) DESC, m.title ASC LIMIT 1;这里有两个细节要解释。一个是日期范围我用的是半开区间 2020-02-01 AND 2020-03-01这样可以把整个2月包进去而且不会误包含2020年3月1日零点之后的数据。如果写成BETWEEN 2020-02-01 AND 2020-02-29看起来也可以但总感觉不够通用碰到闰年、非闰年还得特意去数天数。后面我会对比更多写法。另一个细节是AVG(mr.rating)。rating字段一般是整数比如1到5分AVG之后会得到一个带小数的数值比如3.6667。排序的时候拿这个小数比较完全没问题。但要注意如果某个电影在2月只有一条评分记录它的平均分就等于这条评分的原始值如果一部电影在2月没有评分记录它就不会出现在分组结果里自然也不可能被选中这符合题意。2.3 第三步合并结果集LeetCode这道题的输出不是一个表分成两列而是把两个结果纵向合并成一列。所以需要用到UNION ALL。很多SQL新手会在这里卡一下因为两个查询的列名都叫results合并起来倒是不难(SELECT u.name AS results FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ORDER BY COUNT(*) DESC, u.name ASC LIMIT 1) UNION ALL (SELECT m.title AS results FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-02-01 AND mr.created_at 2020-03-01 GROUP BY m.movie_id, m.title ORDER BY AVG(mr.rating) DESC, m.title ASC LIMIT 1);我习惯给每个子查询加括号因为ORDER BY和LIMIT在UNION里如果不加括号整个语法会有歧义很多数据库会把ORDER BY理解成对整个合并结果的排序那就不对了。虽然MySQL允许最后一个ORDER BY不写括号但写成括号版本绝对更稳妥。为什么用UNION ALL而不是UNION这两个查询的结果集不存在重复的可能性一个是用户名一个是电影标题即使某个用户名和某个电影标题同名比如一个用户叫“Frozen 2”那也只是字符串巧合数据库不会因为值相同就把两个查询结果合并去重。考虑到通用性和性能UNION ALL不执行去重操作更高效。3. 完整解法与另一种窗口函数思路3.1 标准分组 LIMIT 写法上面分解完标准答案呼之欲出。这版SQL我建议新手先手动抄一遍理解每条语句背后的逻辑SELECT results FROM ( SELECT u.name AS results FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ORDER BY COUNT(*) DESC, u.name ASC LIMIT 1 ) t1 UNION ALL SELECT results FROM ( SELECT m.title AS results FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-02-01 AND mr.created_at 2020-03-01 GROUP BY m.movie_id, m.title ORDER BY AVG(mr.rating) DESC, m.title ASC LIMIT 1 ) t2;这里我在外层又包了一层子查询其实不加这层也完全没问题。加这层的好处是有些OJ系统对UNION后面的ORDER BY解析比较挑剔包一层能让语法更干净也方便后续调试。如果面试时手写直接写原始的两个括号查询合并即可不需要多套一层。这个版本的核心就是GROUP BY ORDER BY COUNT(*) / AVG LIMIT。它把“取Top 1”的逻辑交给数据库排序加截断代码简洁可读性强是我最推荐面试使用的方案。3.2 窗口函数写法如果你已经熟练掌握了窗口函数可以试试这种替代方案。思路是先用子查询或CTE算出聚合结果然后给每一行打上排名标记最后只取排名为1的那一行SELECT results FROM ( SELECT u.name AS results, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, u.name ASC) AS rn FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ) t1 WHERE rn 1 UNION ALL SELECT results FROM ( SELECT m.title AS results, ROW_NUMBER() OVER (ORDER BY AVG(mr.rating) DESC, m.title ASC) AS rn FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-02-01 AND mr.created_at 2020-03-01 GROUP BY m.movie_id, m.title ) t2 WHERE rn 1;注意窗口函数COUNT(*)和AVG()不能在GROUP BY后的SELECT里直接再包一层ROW_NUMBER()吗实际上可以。ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, ...)这句话的意思是先按用户分组聚合得到每个组的聚合值如评论数然后再按这个聚合值进行排序编号。这个操作发生在GROUP BY之后逻辑上完全成立。窗口函数写法的好处是如果你想一次性返回多个并列Top 1只需要把ROW_NUMBER()换成RANK()或DENSE_RANK()。不过这道题明确要求平局取字典序最小的一个所以ROW_NUMBER()恰好够用。面试时提出这种写法能让面试官觉得你对窗口函数有概念属于加分项。3.3 两种写法对比与适用场景我自己用下来的感受是标准分组方案适合所有数据库SQLite、MySQL、PostgreSQL、SQL Server都能跑窗口函数则是8.0之后的主流选择。列出两种写法在几个维度上的差异对比维度GROUP BY LIMIT窗口函数可读性直观容易解释略微抽象需要理解ROW_NUMBER平局处理依赖ORDER BY字段方向同样依赖但可用RANK扩展返回多个并列结果不支持只能取一行换成RANK()即可兼容性MySQL 5.x、SQLite都支持需要MySQL 8.x、PG 8.4等性能通常更快提前截断需要全部分组后再编号如果只是做这道题两种写法的结果完全相同。实际业务中如果只想拿Top 1我一般用ORDER BY LIMIT因为数据库优化器会在排序后直接返回第一条减少数据传输如果需求是“取每个分类的Top N”那窗口函数几乎是唯一优雅的解。4. 实战踩坑日期、分组与排序的细节4.1 2020年2月到底怎么写才不容易错日期过滤是这道题很常见的翻车点。有人写成WHERE YEAR(created_at) 2020 AND MONTH(created_at) 2这样写从结果上看没错但是YEAR()和MONTH()都是函数套在字段上会导致该字段上的索引无法正常使用。数据量小无所谓一旦评分记录达到百万级这种写法可能让查询变成全表扫描。我平时更推荐范围比较WHERE created_at 2020-02-01 AND created_at 2020-03-01这个写法有两个好处一是索引友好数据库可以直接用created_at字段的B树索引进行范围扫描二是无需关心2020年是否为闰年不用纠结2020-02-29存不存在。如果你非要用BETWEEN写BETWEEN 2020-02-01 AND 2020-02-29也能跑但少了一天如果2月有29日或者多包含一天的事很难直观判断。区间写法是SQL查询日期范围的最稳姿势。补充一个细节如果created_at字段是datetime类型哪怕里面存的是2020-02-01 12:30:00用 2020-02-01也能正确匹配因为字符串会自动转为日期时间类型。如果是date类型这个问题更简单。总之范围条件是首选。4.2 分组时的字段选择为什么 GROUP BY user_id, name我见过很多代码只写GROUP BY u.user_id然后SELECT u.name。在老版本的MySQL里这种行为允许但一旦开启ONLY_FULL_GROUP_BYMySQL 5.7以后默认开启这种写法会直接报错。更重要的是即使不报错把两个字段一起分组在逻辑上也更严谨因为user_id与name虽然是一一对应的但显式表达“我们这个分组维度是用户ID和用户名”更清晰。同理第二个查询GROUP BY m.movie_id, m.title也是这个道理。分组字段写得完整后续如果要加筛选条件或者在SQL中复用这个结果不容易出现模棱两可的字段引用。4.3 ORDER BY 与平局DESC/ASC 别搞反拿到这道题有人会想“取数量最多”就用COUNT(*) DESC这个没问题。但平局时“字典序最小”应该是按ASC排因为字典序从小到大正好是最小排在最前。有人会下意识觉得“最”都应该用DESC结果写成了name DESC导致平局时取到字典序最大的名字直接答案错误。电影标题同理AVG(rating) DESC取最高平均分title ASC处理平局。这里有一个容易忽略的点ORDER BY AVG(rating) DESC排序时如果两部电影平均分相同title ASC决定谁排前面。但如果title本身很长有空格或大小写问题不同数据库的字典序排序规则可能有细微差异面试时一般忽略实际项目中如果遇到就需要注意排序规则collation设置。4.4 如果数据量大怎么优化索引虽然LeetCode的测试数据不大但作为经常写SQL的人我们还是应该思考优化。MovieRating表的常见查询路径有两个一是按user_id分组统计评论数二是按movie_id和created_at过滤计算平均分。针对这两个需求可以建立联合索引。第一个查询的加速索引是(user_id)因为分组字段是user_id第二个查询的加速索引是(movie_id, created_at)或者(created_at, movie_id)。具体选择哪组取决于过滤条件里是否用created_at限制范围然后再按movie_id分组。如果查询条件是先过滤日期那(created_at, movie_id)更合适如果是要查特定电影的评分趋势那(movie_id, created_at)更合适。这道题的第二个查询是“先限定期限再按电影分组”所以(created_at, movie_id)理论上更贴合。当然索引不是越多越好写面试答案时不提索引也可以面试官更看重SQL正确性和思路。但如果你主动说一句“如果评分表数据量大可以考虑在created_at和movie_id上建立联合索引”这会是一个很亮眼的加分项。5. 面试官可能会追问的延伸问题5.1 如果平局时想返回所有符合条件的名字这是一个很自然的引申问题。假设有两个用户评论数并列第一题目却只让返回一个所以用了LIMIT 1。但如果需求变成“返回所有并列最高的用户”LIMIT就没办法了。这时窗口函数版可以轻松改造成SELECT name FROM ( SELECT u.name AS name, RANK() OVER (ORDER BY COUNT(*) DESC, u.name ASC) AS rk FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ) t WHERE rk 1;这里把ROW_NUMBER()换成RANK()并把WHERE rn 1改成WHERE rk 1就能返回所有并列用户。但要注意题目明确说平局取字典序最小所以这道题还是用ROW_NUMBER()更精确。面试中主动向面试官展示这种改动说明你理解ROW_NUMBER和RANK的区别会非常加分。5.2 如果要求2020年每个月评分最高的电影这个问题比原题多了一个维度月份。我们需要按月份和电影两个维度分组然后找出每个月平均分最高的电影。一种简单粗暴的做法是分别写出12条查询再用UNION ALL合并但这显然不优雅。窗口函数在这里可以大显身手SELECT month, title, avg_rating FROM ( SELECT DATE_FORMAT(mr.created_at, %Y-%m) AS month, m.title AS title, AVG(mr.rating) AS avg_rating, ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(mr.created_at, %Y-%m) ORDER BY AVG(mr.rating) DESC, m.title ASC) AS rn FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-01-01 AND mr.created_at 2021-01-01 GROUP BY DATE_FORMAT(mr.created_at, %Y-%m), m.movie_id, m.title ) t WHERE rn 1 ORDER BY month;注意窗口函数里的PARTITION BY表示按月份分组排序也就是每个月内部独立排名。这个例子把分组和窗口函数结合得比较深如果面试官顺着原题问出这种变体你能写出来基本就稳了。5.3 如果既要查用户又要查电影能一次扫描搞定吗原题分两步因为统计口径完全不同一个是统计全部日期的评论数一个是统计特定月份的平均分。这种场景下很难做到一次扫描同时算出两个结果。但有一种巧妙的思路先对MovieRating表做一次完整聚合生成一个临时的结果集再分别从临时结果集里取数据。比如先按用户分组得到评论数再按电影和月份分组得到月度平均分这两个子查询都用的是原始表扫描不可避免。在业务层面这种需求往往可以拆成两个独立任务分别跑定时任务把结果写到结果表里再在展示层拼接。这道题主要考SQL基本功我们不用过度设计性能优化能给出两种正确解法就已经超过大多数人。最后补充一点我自己的练习心得这道题我踩过的最大坑其实是“想当然”。第一次看到题目时我以为第二个查询只需要对MovieRating表按movie_id分组求平均结果忘了先过滤2020年2月导致结果完全错误。后来我把题目里的每一个限定词都用记号笔画出来再对着SQL一句一句检查才真正理解了这类“业务描述转SQL”的题目该怎么拆解。如果你也在准备面试建议不要只背答案而是把这道题的三个考点写在一张便签上多表连接、聚合排序、日期范围。每次刷SQL题都下意识检查这三个点会很有帮助。另外一个实用小技巧是遇到UNION合并结果时先单独跑两个子查询确认各自结果正确再合并排错效率会高很多。这些经验希望能帮少走一点弯路尤其是刚刷LeetCode SQL专题的朋友。
LeetCode 1341 SQL题详解：电影评分中的分组聚合与排序陷阱
LeetCode 1341这道题是我刷SQL专题时遇到的一道很典型的“看似简单、实则坑多”的题目。名字叫“电影评分”很多人以为是写个AVG就完事结果卡在了“平局时返回字典序最小”这个规则上。这道题把多表连接、分组聚合、日期过滤、排序取Top 1这几个SQL高频考点全揉在了一起非常适合用来检验你对SQL执行顺序和边界条件的理解。我前前后后刷了三种写法踩过几个小坑今天把完整的拆解过程写下来给准备面试或者想巩固SQL基础的朋友做个参考。1. 先看懂题目三张表与两个问题1.1 表结构与实体关系原题给了三张表我用最直白的方式给大家还原一下。Movies表保存电影信息字段是movie_id电影主键和title电影标题。Users表保存用户信息字段是user_id用户主键和name用户名。MovieRating表是评分记录字段有movie_id、user_id、rating以及created_at而且这张表的主键是(movie_id, user_id)也就是说同一个用户对同一部电影只会有一条评分记录。这三张表的关系其实很清晰MovieRating是中间关联表movie_id引用Movies表user_id引用Users表。用数据库ER图的语言来说MovieRating和Movies是多对一关系MovieRating和Users也是多对一关系。换句话说一个用户可以评分多部电影一部电影可以被多个用户评分但用户和电影之间的评分关系只有一条。这里有个细节值得注意题目里说“评论电影数量最多”在MovieRating表里每一条记录就是一次评分也就是一次评论行为。因为表结构已经限制了同一用户对同一电影只能评一次所以只要统计每个用户在MovieRating表中出现的行数就能得到他评论过的电影数量。后面写SQL的时候直接COUNT(*)即可不需要去重。1.2 两个查询条件拆解题目要我们输出两个结果并且合并到一列results中。第一个问题是找到评论电影数量最多的用户名如果平局返回字典序最小的用户名。第二个问题是找到2020年2月平均评分最高的电影标题如果平局返回字典序最小的电影标题。第一眼看上去两个问题都可以用“分组聚合 排序 LIMIT 1”解决。第一个问题按用户分组统计数量按数量降序、用户名升序排取第一行。第二个问题按电影分组计算平均分按平均分降序、电影标题升序排取第一行。思路确实是这样但实际操作起来有几个地方容易犯迷糊。比如第二个问题很多人会忘记限定“2020年2月”这个时间窗口直接对所有时间的数据求平均分。还有一些人虽然记得过滤日期但写成了WHERE YEAR(created_at)2020 AND MONTH(created_at)2这样在数据量大的场景下可能会导致索引失效。后面第五章节我会专门聊这个坑。1.3 平局规则是这道题的真正考点如果只考“取最大”题目就太简单了所以它特意加了平局规则结果一样时按照字典序取最小的名字或标题。这个规则用ORDER BY很容易实现但很多人会漏掉或者排序字段方向写反。举个例子有两个用户A和B评论数量都是5条。我们想要的是两者之间名字靠前的那一个也就是按name ASC排列后的第一个。但如果只写ORDER BY COUNT(*) DESC数据库返回的顺序是不确定的可能返回A也可能返回B这就不符合题目要求了。正确的做法是ORDER BY COUNT(*) DESC, name ASC LIMIT 1。先按评论数降序排再按名字升序排取第一行这样平局时一定拿到字典序最小的那个。同理电影标题平局时也要按title ASC取第一个。这个排序规则是整个题目的灵魂理解了它后面的SQL就是一马平川。2. 从执行计划反推 SQL 写作顺序2.1 第一步先写“评论最多的用户”我们先不考虑合并结果分步实现。第一个查询的SQL可以这样写SELECT u.name AS results FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ORDER BY COUNT(*) DESC, u.name ASC LIMIT 1;这里我直接用了内连接JOIN因为题目要找的是“评论过电影的用户”没评论过的用户根本不可能成为最大评论者。GROUP BY后面为什么带u.name因为MySQL等多数数据库在ONLY_FULL_GROUP_BY模式下如果SELECT里出现了u.nameGROUP BY就必须包含它否则直接报错。虽然按u.user_id分组理论上u.name是唯一的但为了兼容性和可读性建议分组字段写全。ORDER BY COUNT(*) DESC是核心它统计每个用户的评论记录数然后从多到少排列。u.name ASC处理平局。LIMIT 1取第一个。执行顺序上数据库先做JOIN把Users和MovieRating关联起来然后GROUP BY分组对每组执行聚合计数接着ORDER BY排序最后LIMIT截取第一行。这个顺序想清楚就不会纠结为什么WHERE不能过滤聚合结果了。2.2 第二步写“2020年2月平均分最高的电影”第二个查询的SQL如下SELECT m.title AS results FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-02-01 AND mr.created_at 2020-03-01 GROUP BY m.movie_id, m.title ORDER BY AVG(mr.rating) DESC, m.title ASC LIMIT 1;这里有两个细节要解释。一个是日期范围我用的是半开区间 2020-02-01 AND 2020-03-01这样可以把整个2月包进去而且不会误包含2020年3月1日零点之后的数据。如果写成BETWEEN 2020-02-01 AND 2020-02-29看起来也可以但总感觉不够通用碰到闰年、非闰年还得特意去数天数。后面我会对比更多写法。另一个细节是AVG(mr.rating)。rating字段一般是整数比如1到5分AVG之后会得到一个带小数的数值比如3.6667。排序的时候拿这个小数比较完全没问题。但要注意如果某个电影在2月只有一条评分记录它的平均分就等于这条评分的原始值如果一部电影在2月没有评分记录它就不会出现在分组结果里自然也不可能被选中这符合题意。2.3 第三步合并结果集LeetCode这道题的输出不是一个表分成两列而是把两个结果纵向合并成一列。所以需要用到UNION ALL。很多SQL新手会在这里卡一下因为两个查询的列名都叫results合并起来倒是不难(SELECT u.name AS results FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ORDER BY COUNT(*) DESC, u.name ASC LIMIT 1) UNION ALL (SELECT m.title AS results FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-02-01 AND mr.created_at 2020-03-01 GROUP BY m.movie_id, m.title ORDER BY AVG(mr.rating) DESC, m.title ASC LIMIT 1);我习惯给每个子查询加括号因为ORDER BY和LIMIT在UNION里如果不加括号整个语法会有歧义很多数据库会把ORDER BY理解成对整个合并结果的排序那就不对了。虽然MySQL允许最后一个ORDER BY不写括号但写成括号版本绝对更稳妥。为什么用UNION ALL而不是UNION这两个查询的结果集不存在重复的可能性一个是用户名一个是电影标题即使某个用户名和某个电影标题同名比如一个用户叫“Frozen 2”那也只是字符串巧合数据库不会因为值相同就把两个查询结果合并去重。考虑到通用性和性能UNION ALL不执行去重操作更高效。3. 完整解法与另一种窗口函数思路3.1 标准分组 LIMIT 写法上面分解完标准答案呼之欲出。这版SQL我建议新手先手动抄一遍理解每条语句背后的逻辑SELECT results FROM ( SELECT u.name AS results FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ORDER BY COUNT(*) DESC, u.name ASC LIMIT 1 ) t1 UNION ALL SELECT results FROM ( SELECT m.title AS results FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-02-01 AND mr.created_at 2020-03-01 GROUP BY m.movie_id, m.title ORDER BY AVG(mr.rating) DESC, m.title ASC LIMIT 1 ) t2;这里我在外层又包了一层子查询其实不加这层也完全没问题。加这层的好处是有些OJ系统对UNION后面的ORDER BY解析比较挑剔包一层能让语法更干净也方便后续调试。如果面试时手写直接写原始的两个括号查询合并即可不需要多套一层。这个版本的核心就是GROUP BY ORDER BY COUNT(*) / AVG LIMIT。它把“取Top 1”的逻辑交给数据库排序加截断代码简洁可读性强是我最推荐面试使用的方案。3.2 窗口函数写法如果你已经熟练掌握了窗口函数可以试试这种替代方案。思路是先用子查询或CTE算出聚合结果然后给每一行打上排名标记最后只取排名为1的那一行SELECT results FROM ( SELECT u.name AS results, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, u.name ASC) AS rn FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ) t1 WHERE rn 1 UNION ALL SELECT results FROM ( SELECT m.title AS results, ROW_NUMBER() OVER (ORDER BY AVG(mr.rating) DESC, m.title ASC) AS rn FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-02-01 AND mr.created_at 2020-03-01 GROUP BY m.movie_id, m.title ) t2 WHERE rn 1;注意窗口函数COUNT(*)和AVG()不能在GROUP BY后的SELECT里直接再包一层ROW_NUMBER()吗实际上可以。ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, ...)这句话的意思是先按用户分组聚合得到每个组的聚合值如评论数然后再按这个聚合值进行排序编号。这个操作发生在GROUP BY之后逻辑上完全成立。窗口函数写法的好处是如果你想一次性返回多个并列Top 1只需要把ROW_NUMBER()换成RANK()或DENSE_RANK()。不过这道题明确要求平局取字典序最小的一个所以ROW_NUMBER()恰好够用。面试时提出这种写法能让面试官觉得你对窗口函数有概念属于加分项。3.3 两种写法对比与适用场景我自己用下来的感受是标准分组方案适合所有数据库SQLite、MySQL、PostgreSQL、SQL Server都能跑窗口函数则是8.0之后的主流选择。列出两种写法在几个维度上的差异对比维度GROUP BY LIMIT窗口函数可读性直观容易解释略微抽象需要理解ROW_NUMBER平局处理依赖ORDER BY字段方向同样依赖但可用RANK扩展返回多个并列结果不支持只能取一行换成RANK()即可兼容性MySQL 5.x、SQLite都支持需要MySQL 8.x、PG 8.4等性能通常更快提前截断需要全部分组后再编号如果只是做这道题两种写法的结果完全相同。实际业务中如果只想拿Top 1我一般用ORDER BY LIMIT因为数据库优化器会在排序后直接返回第一条减少数据传输如果需求是“取每个分类的Top N”那窗口函数几乎是唯一优雅的解。4. 实战踩坑日期、分组与排序的细节4.1 2020年2月到底怎么写才不容易错日期过滤是这道题很常见的翻车点。有人写成WHERE YEAR(created_at) 2020 AND MONTH(created_at) 2这样写从结果上看没错但是YEAR()和MONTH()都是函数套在字段上会导致该字段上的索引无法正常使用。数据量小无所谓一旦评分记录达到百万级这种写法可能让查询变成全表扫描。我平时更推荐范围比较WHERE created_at 2020-02-01 AND created_at 2020-03-01这个写法有两个好处一是索引友好数据库可以直接用created_at字段的B树索引进行范围扫描二是无需关心2020年是否为闰年不用纠结2020-02-29存不存在。如果你非要用BETWEEN写BETWEEN 2020-02-01 AND 2020-02-29也能跑但少了一天如果2月有29日或者多包含一天的事很难直观判断。区间写法是SQL查询日期范围的最稳姿势。补充一个细节如果created_at字段是datetime类型哪怕里面存的是2020-02-01 12:30:00用 2020-02-01也能正确匹配因为字符串会自动转为日期时间类型。如果是date类型这个问题更简单。总之范围条件是首选。4.2 分组时的字段选择为什么 GROUP BY user_id, name我见过很多代码只写GROUP BY u.user_id然后SELECT u.name。在老版本的MySQL里这种行为允许但一旦开启ONLY_FULL_GROUP_BYMySQL 5.7以后默认开启这种写法会直接报错。更重要的是即使不报错把两个字段一起分组在逻辑上也更严谨因为user_id与name虽然是一一对应的但显式表达“我们这个分组维度是用户ID和用户名”更清晰。同理第二个查询GROUP BY m.movie_id, m.title也是这个道理。分组字段写得完整后续如果要加筛选条件或者在SQL中复用这个结果不容易出现模棱两可的字段引用。4.3 ORDER BY 与平局DESC/ASC 别搞反拿到这道题有人会想“取数量最多”就用COUNT(*) DESC这个没问题。但平局时“字典序最小”应该是按ASC排因为字典序从小到大正好是最小排在最前。有人会下意识觉得“最”都应该用DESC结果写成了name DESC导致平局时取到字典序最大的名字直接答案错误。电影标题同理AVG(rating) DESC取最高平均分title ASC处理平局。这里有一个容易忽略的点ORDER BY AVG(rating) DESC排序时如果两部电影平均分相同title ASC决定谁排前面。但如果title本身很长有空格或大小写问题不同数据库的字典序排序规则可能有细微差异面试时一般忽略实际项目中如果遇到就需要注意排序规则collation设置。4.4 如果数据量大怎么优化索引虽然LeetCode的测试数据不大但作为经常写SQL的人我们还是应该思考优化。MovieRating表的常见查询路径有两个一是按user_id分组统计评论数二是按movie_id和created_at过滤计算平均分。针对这两个需求可以建立联合索引。第一个查询的加速索引是(user_id)因为分组字段是user_id第二个查询的加速索引是(movie_id, created_at)或者(created_at, movie_id)。具体选择哪组取决于过滤条件里是否用created_at限制范围然后再按movie_id分组。如果查询条件是先过滤日期那(created_at, movie_id)更合适如果是要查特定电影的评分趋势那(movie_id, created_at)更合适。这道题的第二个查询是“先限定期限再按电影分组”所以(created_at, movie_id)理论上更贴合。当然索引不是越多越好写面试答案时不提索引也可以面试官更看重SQL正确性和思路。但如果你主动说一句“如果评分表数据量大可以考虑在created_at和movie_id上建立联合索引”这会是一个很亮眼的加分项。5. 面试官可能会追问的延伸问题5.1 如果平局时想返回所有符合条件的名字这是一个很自然的引申问题。假设有两个用户评论数并列第一题目却只让返回一个所以用了LIMIT 1。但如果需求变成“返回所有并列最高的用户”LIMIT就没办法了。这时窗口函数版可以轻松改造成SELECT name FROM ( SELECT u.name AS name, RANK() OVER (ORDER BY COUNT(*) DESC, u.name ASC) AS rk FROM Users u JOIN MovieRating mr ON u.user_id mr.user_id GROUP BY u.user_id, u.name ) t WHERE rk 1;这里把ROW_NUMBER()换成RANK()并把WHERE rn 1改成WHERE rk 1就能返回所有并列用户。但要注意题目明确说平局取字典序最小所以这道题还是用ROW_NUMBER()更精确。面试中主动向面试官展示这种改动说明你理解ROW_NUMBER和RANK的区别会非常加分。5.2 如果要求2020年每个月评分最高的电影这个问题比原题多了一个维度月份。我们需要按月份和电影两个维度分组然后找出每个月平均分最高的电影。一种简单粗暴的做法是分别写出12条查询再用UNION ALL合并但这显然不优雅。窗口函数在这里可以大显身手SELECT month, title, avg_rating FROM ( SELECT DATE_FORMAT(mr.created_at, %Y-%m) AS month, m.title AS title, AVG(mr.rating) AS avg_rating, ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(mr.created_at, %Y-%m) ORDER BY AVG(mr.rating) DESC, m.title ASC) AS rn FROM Movies m JOIN MovieRating mr ON m.movie_id mr.movie_id WHERE mr.created_at 2020-01-01 AND mr.created_at 2021-01-01 GROUP BY DATE_FORMAT(mr.created_at, %Y-%m), m.movie_id, m.title ) t WHERE rn 1 ORDER BY month;注意窗口函数里的PARTITION BY表示按月份分组排序也就是每个月内部独立排名。这个例子把分组和窗口函数结合得比较深如果面试官顺着原题问出这种变体你能写出来基本就稳了。5.3 如果既要查用户又要查电影能一次扫描搞定吗原题分两步因为统计口径完全不同一个是统计全部日期的评论数一个是统计特定月份的平均分。这种场景下很难做到一次扫描同时算出两个结果。但有一种巧妙的思路先对MovieRating表做一次完整聚合生成一个临时的结果集再分别从临时结果集里取数据。比如先按用户分组得到评论数再按电影和月份分组得到月度平均分这两个子查询都用的是原始表扫描不可避免。在业务层面这种需求往往可以拆成两个独立任务分别跑定时任务把结果写到结果表里再在展示层拼接。这道题主要考SQL基本功我们不用过度设计性能优化能给出两种正确解法就已经超过大多数人。最后补充一点我自己的练习心得这道题我踩过的最大坑其实是“想当然”。第一次看到题目时我以为第二个查询只需要对MovieRating表按movie_id分组求平均结果忘了先过滤2020年2月导致结果完全错误。后来我把题目里的每一个限定词都用记号笔画出来再对着SQL一句一句检查才真正理解了这类“业务描述转SQL”的题目该怎么拆解。如果你也在准备面试建议不要只背答案而是把这道题的三个考点写在一张便签上多表连接、聚合排序、日期范围。每次刷SQL题都下意识检查这三个点会很有帮助。另外一个实用小技巧是遇到UNION合并结果时先单独跑两个子查询确认各自结果正确再合并排错效率会高很多。这些经验希望能帮少走一点弯路尤其是刚刷LeetCode SQL专题的朋友。
