我最近在刷牛客网SQL题库刷到SQL40“每个月Top3的周杰伦歌曲”时停了一下。表面上是给周杰伦做月度排行榜底下其实是一道非常标准的MySQL分组TopN查询按月份分组组内按播放次数排序每组只保留前三条。这类需求在后台系统里太常见了——每个门店销量TOP3商品、每个部门薪资前三名、每个用户的最近三笔订单全是同一套逻辑。现在很多人习惯让DeepSeek这类AI工具直接生成SQL我也这么干但我不建议拿到答案就交卷因为分组TopN的版本差异、并列名次语义、聚合与窗口函数的执行顺序恰好是AI最容易翻车的地方。这篇就把这道题从题目还原、三种实现方案、并列名次到底怎么排到生产环境里的踩坑经验全部讲透适合正在刷题准备面试或者日常要写报表查数的朋友。1. 先把SQL40的题目拆明白两张表、一个需求1.1 表结构和样例数据牛客网SQL40各个版本的表名、字段名偶尔会有差异但我见过的常见结构基本是下面这两张表。我自己做这道题时习惯先把表结构画出来再往里面填数据因为SQL写着写着容易忘掉“这张表到底有什么字段”。表1歌曲信息表song_info字段类型说明song_idint歌曲ID主键song_namevarchar歌曲名称singervarchar歌手名称表2用户听歌记录表listen_record字段类型说明user_idint用户IDsong_idint哪首歌关联song_info.song_idlisten_datedatetime听歌时间为了后面能实跑我先造一组小数据。周杰伦的歌放5首晴天、七里香、稻香、青花瓷、告白气球。再放两首别人的歌比如孤勇者和小苹果用来验证“只统计周杰伦”这个过滤条件真的生效。听歌记录里让2024年1月和2月各有几天、不同歌曲有不同播放次数播放次数的差异要能看出名次。具体的建表和插入语句我放到第3章统一给这一章先把需求本身讲透。1.2 需求翻译成SQL执行链题目的中文描述往往是统计每个月播放次数Top3的周杰伦歌曲。这句话信息量很大我习惯拆成四步过滤只要si.singer 周杰伦的歌聚合按月份 歌曲名分组用COUNT(*)统计每首歌每个月的播放次数排名在每个月内部按播放次数从高到低排序截断每组只留下排名前3的歌输出月份、歌曲名、播放次数。如果你把第1步和第2步直接写成WHERE singer周杰伦 GROUP BY month, song_name就已经完成了一大半。真正的难点在第3、4步“组内排名”不是SQL天然支持的动作它需要在聚合结果之上再做一次“分组排序”。这就是分组TopN和普通GROUP BY的区别也是这道题想考的能力。加深理解可以这样想普通的GROUP BY会把一个组折叠成一行组内明细就丢了而TopN要求“一个组保留多行且行数有限制”相当于在做完聚合之后还要保留一个组内的“位置感”。这个位置感MySQL 8.0用窗口函数可以轻松给5.7则要绕路。下一节我把三种主流做法都列出来。2. 分组TopN的三条技术路线窗口函数、用户变量、自连接2.1 窗口函数先确认你的MySQL是不是8.0窗口函数是MySQL 8.0开始才有的能力。拿到题先确认版本一条SELECT VERSION();就能看出来。如果是8.0及以上优先用ROW_NUMBER()SELECT month, song_name, play_cnt FROM ( SELECT DATE_FORMAT(lr.listen_date, %Y-%m) AS month, si.song_name AS song_name, COUNT(*) AS play_cnt, ROW_NUMBER() OVER ( PARTITION BY DATE_FORMAT(lr.listen_date, %Y-%m) ORDER BY COUNT(*) DESC ) AS rnk FROM listen_record lr JOIN song_info si ON lr.song_id si.song_id WHERE si.singer 周杰伦 GROUP BY month, si.song_name ) t WHERE rnk 3 ORDER BY month, play_cnt DESC;这段代码的执行顺序值得逐层看因为很多人看到WHERE rnk 3会愣一下WHERE不是应该先执行吗为什么可以用别名rnk搞清楚顺序就明白了。MySQL的SQL逻辑执行顺序大致是先FROM→WHERE→GROUP BY→ 聚合 →HAVING→ 窗口函数 →SELECT→ORDER BY→LIMIT。窗口函数是在GROUP BY聚合完成之后才计算的所以子查询里生成的rnk外层WHERE可以放心用如果你试图在同一个查询里写WHERE rnk 3而不包子查询就会报Unknown column因为窗口函数生成的结果在WHERE阶段还不可见。这个点我在带新人时讲过很多次属于TopN题的经典易错点。窗口函数的好处是可读性强、性能好、语义清晰。PARTITION BY是“按什么分组”ORDER BY是“组内按什么排序”两个拼起来正好就是“组内排名”的意思。这道题里PARTITION BY用月份ORDER BY用每首歌的播放次数降序排完序后组内第1行就是当月播放量最高的周杰伦歌曲。2.2 用户变量MySQL 5.7时代的民间写法MySQL 5.7没有窗口函数但题目又经常在5.7环境里考这时候有两条路一是自连接二就是用用户变量模拟行号。用户变量写法虽然不那么正统但在生产库升级到8.0之前很多老系统里就是这么跑的。核心思路是先把数据按“月份播放次数降序”排好序然后用变量按顺序“数数”——只要发现月份变了就从1重新数月份没变就继续加一。SELECT month, song_name, play_cnt FROM ( SELECT t.month, t.song_name, t.play_cnt, rnk : IF(prev_month t.month, rnk 1, 1) AS rnk, prev_month : t.month FROM ( SELECT DATE_FORMAT(lr.listen_date, %Y-%m) AS month, si.song_name AS song_name, COUNT(*) AS play_cnt FROM listen_record lr JOIN song_info si ON lr.song_id si.song_id WHERE si.singer 周杰伦 GROUP BY month, si.song_name ORDER BY month, play_cnt DESC ) t CROSS JOIN (SELECT prev_month : NULL, rnk : 0) r ) ranked WHERE rnk 3;有两点必须提醒。第一内层子查询的ORDER BY month, play_cnt DESC绝对不能省因为用户变量的“数数”只有在数据有序时才成立如果你忘了排序排名结果会完全错乱。第二SELECT里先算rnk、再更新prev_month这个顺序依赖MySQL对SELECT列的实际求值顺序官方文档并没有做出确定性保证也就是说它属于“实践上能用、但不推荐写成正式规范”的写法。我在本地MySQL 5.7上验证过可用但如果要在生产环境用这种方案建议先在测试库跑一遍完整回归。2.3 自连接逻辑最直白但性能最差还有一种思路不用任何窗口函数和变量全程只用基础SELECT。它的核心判断是“播放次数比当前歌曲高的歌在同一个月份里不足3首那当前歌曲就排进了Top3”。这个判断完全符合排名定义。SELECT a.month, a.song_name, a.play_cnt FROM ( SELECT DATE_FORMAT(lr.listen_date, %Y-%m) AS month, si.song_name AS song_name, COUNT(*) AS play_cnt FROM listen_record lr JOIN song_info si ON lr.song_id si.song_id WHERE si.singer 周杰伦 GROUP BY month, si.song_name ) a WHERE ( SELECT COUNT(*) FROM ( SELECT DATE_FORMAT(lr.listen_date, %Y-%m) AS month, si.song_name AS song_name, COUNT(*) AS play_cnt FROM listen_record lr JOIN song_info si ON lr.song_id si.song_id WHERE si.singer 周杰伦 GROUP BY month, si.song_name ) b WHERE b.month a.month AND b.play_cnt a.play_cnt ) 3 ORDER BY a.month, a.play_cnt DESC;这段SQL的可读性其实不错b.play_cnt a.play_cnt就是在数“有多少首歌比我火”。但问题也很明显每个分组的每一行都要执行一遍子查询去扫描同一个聚合结果数据量一上来执行时间会肉眼可见地变慢。另外这里的语义其实是DENSE_RANK的语义播放次数相同不算“比我火”所以并列的歌会一起进Top3。如果想完全复刻ROW_NUMBER的“严格只取3条”还得在并列时再加一个song_name的次序条件SQL会更长更绕。我用这种方法一般只为了两件事环境里MySQL版本太老连5.5都不支持变量或者只是想用最简单的基础语法验证业务逻辑。真正上线还是优先窗口函数。2.4 三种方案横向对比方案依赖版本可读性性能并列语义推荐度ROW_NUMBER()窗口函数MySQL 8.0高好无并列固定不重复序号有8.0环境首选用户变量模拟MySQL 5.x可用中中等数据有序时按顺序编号无并列处理老版本临时方案自连接相关子查询任何版本中较差天然等同DENSE_RANK并列会保留数据量小、验证逻辑时使用我做这道题时第一反应是用窗口函数因为牛客网在线编辑器通常就是MySQL 8.0环境。如果是在老项目里碰到同样需求再考虑用户变量并且务必做好注释过三个月回来看这段代码会像天书。3. 可复现实战造数、跑题、看中间结果3.1 建表加造数把题目变成能跑的本地Demo光看代码不如自己跑一遍。我把完整Demo贴在下面直接粘到MySQL里就能跑。造数时我特意让1月和2月的播放次数分布不一样并混入了非周杰伦的歌来验证过滤逻辑CREATE TABLE song_info ( song_id INT PRIMARY KEY, song_name VARCHAR(50), singer VARCHAR(50) ); CREATE TABLE listen_record ( user_id INT, song_id INT, listen_date DATETIME, KEY idx_song (song_id) ); INSERT INTO song_info VALUES (1, 晴天, 周杰伦), (2, 七里香, 周杰伦), (3, 稻香, 周杰伦), (4, 青花瓷, 周杰伦), (5, 告白气球, 周杰伦), (6, 孤勇者, 陈奕迅), (7, 小苹果, 筷子兄弟); -- 1月记录稻香5次、晴天4次、七里香3次、告白气球2次、青花瓷1次 INSERT INTO listen_record VALUES (101,1,2024-01-01 10:00:00), (102,1,2024-01-01 11:00:00), (103,1,2024-01-02 10:00:00), (104,1,2024-01-03 10:00:00), (101,2,2024-01-01 12:00:00), (102,2,2024-01-01 13:00:00), (103,2,2024-01-03 14:00:00), (101,3,2024-01-02 09:00:00), (102,3,2024-01-02 10:00:00), (103,3,2024-01-03 11:00:00), (104,3,2024-01-04 12:00:00), (105,3,2024-01-05 13:00:00), (101,4,2024-01-06 10:00:00), (101,5,2024-01-04 15:00:00), (102,5,2024-01-05 16:00:00), (101,6,2024-01-07 10:00:00), (102,6,2024-01-08 10:00:00), (101,7,2024-01-09 10:00:00); -- 2月记录晴天6次、青花瓷4次、告白气球3次、七里香1次、稻香1次 INSERT INTO listen_record VALUES (101,1,2024-02-01 10:00:00), (102,1,2024-02-01 11:00:00), (103,1,2024-02-02 12:00:00), (104,1,2024-02-02 13:00:00), (105,1,2024-02-03 14:00:00), (106,1,2024-02-03 15:00:00), (101,2,2024-02-04 10:00:00), (101,3,2024-02-05 10:00:00), (101,4,2024-02-01 09:00:00), (102,4,2024-02-02 10:00:00), (103,4,2024-02-03 11:00:00), (104,4,2024-02-04 12:00:00), (101,5,2024-02-05 13:00:00), (102,5,2024-02-06 14:00:00), (103,5,2024-02-07 15:00:00), (101,6,2024-02-08 10:00:00), (102,6,2024-02-08 11:00:00), (103,6,2024-02-09 12:00:00);这段数据的好处是每个月各歌曲的播放次数清清楚楚没有并列干扰方便你先验证正确性。后面第4章我再单独构造并列场景来折腾排名函数。3.2 中间层验证先看聚合统计对不对拿到题目不要直接套最终答案先跑一层聚合确认“这个月每首歌到底有多少次”没算错SELECT DATE_FORMAT(lr.listen_date, %Y-%m) AS month, si.song_name AS song_name, COUNT(*) AS play_cnt FROM listen_record lr JOIN song_info si ON lr.song_id si.song_id WHERE si.singer 周杰伦 GROUP BY month, si.song_name ORDER BY month, play_cnt DESC;预期结果如下monthsong_nameplay_cnt2024-01稻香52024-01晴天42024-01七里香32024-01告白气球22024-01青花瓷12024-02晴天62024-02青花瓷42024-02告白气球32024-02七里香12024-02稻香1确认这个结果没问题后再往上包窗口函数等于把“排名”这层单独验证给每条记录加上rnk肉眼看1月前3是稻香、晴天、七里香2月前3是晴天、青花瓷、告白气球。注意非周杰伦的歌孤勇者、小苹果从头到尾都没有出现说明WHERE si.singer 周杰伦生效了。3.3 完整答案SQL与最终输出聚合和排名都验证完再把WHERE rnk 3套在外面得到最终答案SELECT month, song_name, play_cnt FROM ( SELECT DATE_FORMAT(lr.listen_date, %Y-%m) AS month, si.song_name AS song_name, COUNT(*) AS play_cnt, ROW_NUMBER() OVER ( PARTITION BY DATE_FORMAT(lr.listen_date, %Y-%m) ORDER BY COUNT(*) DESC ) AS rnk FROM listen_record lr JOIN song_info si ON lr.song_id si.song_id WHERE si.singer 周杰伦 GROUP BY month, si.song_name ) t WHERE rnk 3 ORDER BY month, play_cnt DESC;最终输出monthsong_nameplay_cnt2024-01稻香52024-01晴天42024-01七里香32024-02晴天62024-02青花瓷42024-02告白气球3这个结果符合题目预期每个月只保留播放次数最高的3首周杰伦歌曲。4. 一个细节决定成败并列名次时Top3怎么算4.1 ROW_NUMBER、RANK、DENSE_RANK的区别上面的造数没有并列但真实数据里并列太常见了比如两首歌都是100次。这时候Top3就出问题了因为三个窗口函数对并列的处理逻辑完全不同。构造一个极端场景2024年3月五首歌播放次数分别是晴天100、七里香100、稻香90、青花瓷80、告白气球80。函数晴天七里香稻香青花瓷告白气球ROW_NUMBER12345RANK11344DENSE_RANK11233ROW_NUMBER()给的是一个不重复的“行号”谁排前由排序字段决定并列时顺序不确定甚至可能和插入顺序有关RANK()出现并列时会跳过编号两个第1名之后下一个直接是第3名DENSE_RANK()不跳号两个第1名之后还是第2名。用生活化类比ROW_NUMBER像排队买票两个人同一时刻到也得排先来后到RANK像比赛排名并列第一后面的人就是第三名DENSE_RANK像按分数段划档并列第一后面的人还是第二档。4.2 回到题目Top3到底取几条如果题目里的“Top3”被理解为严格3条那用ROW_NUMBER()最后输出恰好3行如果业务语义是“播放量前三高的歌”并列也算那用DENSE_RANK()可能输出超过3行——比如三首歌并列第一时取前三档可能输出5行。牛客网这道题的标准判题通常基于ROW_NUMBER()的答案输出行数固定。但在实际业务里尤其面试官追问“如果第二第三名并列怎么办”时你要能立刻反应出这个语义差异。我在第2章的自连接解法天然是DENSE_RANK()语义因为“播放次数比我高的歌小于3首”意味着跟我并列的歌不会把彼此挤下去。如果题目要求严格3条就在并列条件下再补一个排序键比如AND ( b.play_cnt a.play_cnt OR (b.play_cnt a.play_cnt AND b.song_name a.song_name) )这就把并列关系变成了“按歌名字母再排一次”相当于手写ROW_NUMBER()。4.3 面试里这道题怎么被升级面试官一般不会止步于“你会写窗口函数”常见的升级问法有几种如果要求每个歌手而不是每个月TopN怎么改——把PARTITION BY从月份换成歌手字段如果数据分散在多张表还有去重、多维度组合怎么办——提前用子查询把维度和指标算好再喂给窗口函数如果MySQL 5.7不能上窗口函数怎么办——现场写用户变量方案如果表有1000万行你的SQL怎么优化——索引、避免大聚合、考虑数仓方案。这些本质上都是在考“你是否真的理解分组TopN在数据库里是怎么执行的”。理解了这点题目怎么变形都不慌。5. 真实业务里的搬砖经验这些坑我是真踩过5.1 月份被GROUP BY丢了一半日期函数的选择这道题的月份格式化最稳妥的是DATE_FORMAT(listen_date, %Y-%m)输出形如2024-01。我第一次写的时候用了LEFT(listen_date, 7)也能出2024-01的效果但隐患在于如果listen_date是DATETIME类型隐式转字符串后截取前7位倒是没问题一旦字段类型被改成TIMESTAMP或者带时区结果可能变样。我个人的习惯是DATETIME就用DATE_FORMAT或EXTRACT(YEAR_MONTH)。最容易被坑的是SELECT里和GROUP BY里必须写同一个表达式。比如SELECT DATE_FORMAT(listen_date,%Y-%m) ... GROUP BY listen_dateMySQL对这样的写法有时不报错但实际上月份分组会变成按完整时间分组结果会出现一堆看起来“只有每个月第一行”的诡异数据。这种错比直接报错更麻烦因为线上查数时很容易忽略。5.2 大数据量下的索引和连接顺序作为刷题牛客网的数据量很小怎么连都行。真到了生产环境listen_record这种流水表很容易上千万行这时候有两个建议listen_record.song_id关联song_info务必有索引推荐(song_id, listen_date)既能加速JOIN也能在需要按时间范围过滤时提供帮助如果频繁做WHERE singer 周杰伦这种过滤给song_info.singer加索引。虽然歌曲表一般不大但过滤条件下推到歌曲表再JOIN记录表可以减少参与聚合的记录条数。顺序上MySQL优化器会自己决定JOIN顺序但你可以用EXPLAIN看执行计划确认它是不是先过滤了周杰伦再关联。如果发现先扫描了整张listen_record多半是统计信息不准或者缺索引。至于窗口函数本身它需要在内存或临时表里做排序数据量大时可以观察EXPLAIN里是否出现Using temporary和Using filesort。如果出现考虑把月份范围缩小或者用预聚合临时表把粒度先降下来。5.3 从SQL40延伸出去的TopN变体分组TopN这题的价值在于它是一类题的母题。我列几个常见变体全都套用同一套框架每个部门薪资Top3PARTITION BY dept_id ORDER BY salary DESC每个店铺销量Top3商品PARTITION BY shop_id ORDER BY cnt DESC每个用户的最近3笔订单先按用户分区、时间倒序取rnk 3每个城市新增用户Top10换成LIMIT语义的变体写法上永远是“先聚合或者直接开窗生成组内排名再外套一层过滤”。唯一要注意的是如果“最近一笔”这种需求里每条记录本身是明细而非聚合可以直接开窗不用GROUP BY。这也是很多人在TopN上翻车的原因——他们习惯性先做一层GROUP BY反而把明细的粒度搞没了。5.4 用DeepSeek这类AI工具写SQL之后我做了什么检查标题里提到DeepSeek我也说说自己实际用AI辅助做题和写SQL的体会。现在问DeepSeek“牛客网SQL40怎么写”它通常很快会给出一版ROW_NUMBER()的答案方向是对的。但我拿到AI答案后不会直接粘至少做三个检查版本检查AI默认给的往往是最新语法如果你的MySQL是5.7窗口函数部分会直接报语法错误执行顺序检查确认rnk是在子查询里生成、外层过滤而不是在同一个SELECT里到处引用别名造数验证把数据塞进临时表跑一遍对比期望结果尤其看并列时三个排名函数的表现差异。AI最适合做的事是帮我解释某一段报错、梳理某种写法的执行逻辑或者把自连接方案翻译成更易读的窗口函数方案。让它完全替代自己对执行逻辑的思考在分组TopN这种细节很多的题目上容易翻车。这道SQL40我前后刷过三遍第一遍只会窗口函数第二遍补上了5.7的用户变量写法第三遍才真正把并列语义和性能影响串起来。如果你也正在刷这类题我的建议就一句话先确认MySQL版本再把聚合和窗口函数之间的执行顺序画清楚最后用一组带并列的测试数据验证自己的答案。想明白这三个点分组TopN的题目基本就稳了。
