MySQL分组TopN查询：从窗口函数到用户变量的完整解法

发布时间：2026/10/7 21:56:06
MySQL分组TopN查询：从窗口函数到用户变量的完整解法
做后端开发的应该没人敢说没写过TopN查询。排行榜、销售报表、推荐商品说到底都是“分组取前几条”。前两天刷牛客网SQL40这道题时我顺手让DeepSeek帮我列了一下分组TopN的几种写法它给的框架很清晰但真到自己动手写SQL还是在执行顺序和版本兼容上踩了几个坑。这道题把月份分组、周杰伦歌曲筛选和Top3排名放在一起属于那种“看起来简单、一写就碎”的经典题目。这篇文章我从题目还原开始把表结构、8.0窗口函数写法、5.7用户变量写法、建表造数、评测通过细节和常见报错全部过一遍。适合正在刷题准备面试的读者也适合想让分组TopN彻底入门的后端开发。文章里所有SQL都可以直接复制到本地跑。1. 先把题目还原看清“每月Top3”到底要做什么1.1 牛客网SQL40的题面与表结构还原牛客网的SQL题目大多数不会把完整表结构写在题干里只给字段说明和数据示例需要自己还原。SQL40这道题我按常见的两表结构拆解一张歌曲信息表song_info包含song_id、song_name、singer_name一张播放记录表play_record包含id、user_id、song_id、play_time。题目要求统计2021年每个月份中播放次数最多的Top3周杰伦歌曲最终输出月份、歌曲名、播放次数。这里有两个隐藏条件需要划重点一是“每个月”不是“每一年”所以分组维度是月份精确到年月二是“周杰伦歌曲”是前提需要在关联后过滤singer_name周杰伦不是所有歌都参与排名。至于播放次数在play_record里每行代表一次播放所以直接COUNT()即可。如果你看到的题目字段略有出入比如把播放次数单独放在一个字段里那就把COUNT()换成SUM(play_cnt)排名逻辑完全一致。1.2 为什么“group by order by limit”一定错新手最容易交出的答案是这个SELECT MONTH(p.play_time) AS m, s.song_name, COUNT(*) AS play_cnt FROM play_record p JOIN song_info s ON p.song_id s.song_id WHERE s.singer_name 周杰伦 AND p.play_time BETWEEN 2021-01-01 AND 2021-12-31 GROUP BY m, s.song_name ORDER BY m ASC, play_cnt DESC LIMIT 3;这段SQL的问题非常隐蔽LIMIT是对整个结果集生效的不是对每个月分组生效。执行完后你只能得到整个结果集中播放次数最高的3行这3行可能全部来自1月也可能来自不同月份反正不是“每月3首”。原因在于MySQL的SQL语义是先执行WHERE、GROUP BY、ORDER BY最后才执行LIMITLIMIT发生在所有分组之后无法感知分组边界。正确做法是先把“组内排名”作为新列算出来再统一过滤排名3。这类需求在标准SQL里就属于“分组TopN”恰好是窗口函数最典型的应用场景。1.3 这道题真正想考的三个能力从面试角度说SQL40想验证的无非三件事。第一是日期处理能力能不能把DATETIME精确提取到月份。我建议用DATE_FORMAT(p.play_time, %Y-%m)而不是YEAR() LPAD(MONTH(), 2, 0)前者更直观也不容易出边界问题。如果题目示例中的月份显示为“2021年1月”就把格式改成%Y年%m月但注意输出格式必须和评测预期完全一致。第二是两表关联和过滤顺序周杰伦歌曲是过滤条件可以放到WHERE里也可以先把song_info里周杰伦的歌曲ID查出来再关联后者在大数据量下更高效。第三是组内排名这部分是最核心的MySQL 8.0用窗口函数老版本用用户变量两种写法都要能讲明白。理解了这三个考点你会发现这道题不是简单背个SQL而是在考你SQL执行计划的底层思维。2. 方案一MySQL 8.0窗口函数分组TopN的标准答案2.1 ROW_NUMBER、RANK、DENSE_RANK到底选哪个窗口函数是MySQL 8.0的大杀器分组TopN用起来几乎是三行代码的事。但很多人在第一步就栽了三个排名函数长得太像不知道选哪个。我用一个考试成绩例子说明。假设一组人的分数是100、100、99ROW_NUMBER()只管编号不管分数相同编号分别是1、2、3。如果你要严格取3名它会随机或按附加排序字段把两个100分排成1和299分排成3看起来合理但相同分数的两个人名次不同。RANK()分数相同给相同名次但下一个名次跳过。两个100分并列第199分排第3。名为“排名”更符合体育比赛习惯。DENSE_RANK()分数相同给相同名次下一个名次不跳过。两个100分并列第199分排第2。适合“凡是达到相同成绩都算同一档”的场景。函数相同分数的排名取前3行数典型场景ROW_NUMBER1、2、3严格3行固定榜单、评测系统RANK1、1、3可能超过3行体育比赛式排名DENSE_RANK1、1、2可能超过3行并列都算的业务报表对于“播放次数Top3歌曲”如果业务上要求并列名次都算应该用DENSE_RANK如果牛客网这种在线评测要求输出固定3行没有并列数据用ROW_NUMBER通过率更高。我后面给的标准SQL用的是ROW_NUMBER因为牛客网判题大多按结果集逐行比对你要保证输出行数可控。2.2 完整SQL先聚合、再窗口、最后过滤下面是我在MySQL 8.0里验证过的完整SQLWITH monthly_cnt AS ( SELECT DATE_FORMAT(p.play_time, %Y-%m) AS months, s.song_name AS song_name, COUNT(*) AS play_cnt FROM play_record p INNER JOIN song_info s ON p.song_id s.song_id WHERE s.singer_name 周杰伦 AND p.play_time 2021-01-01 00:00:00 AND p.play_time 2022-01-01 00:00:00 GROUP BY months, s.song_id, s.song_name ) SELECT months, song_name, play_cnt FROM ( SELECT months, song_name, play_cnt, ROW_NUMBER() OVER ( PARTITION BY months ORDER BY play_cnt DESC, song_name ASC ) AS rn FROM monthly_cnt ) t WHERE rn 3 ORDER BY months ASC, play_cnt DESC, song_name ASC;逐段拆解一下。第一段CTE先做关联、过滤、GROUP BY把原始播放记录压缩成“每个月、每首歌、播放次数”的明细表。这个明细表很小窗口函数只在这个结果集上做排序性能压力小。第二段用ROW_NUMBER开窗PARTITION BY months表示按月分区ORDER BY play_cnt DESC是组内按播放次数降序。注意我在ORDER BY后面补了一个song_name ASC这是给并列条件加的第二排序键保证相同播放次数的歌曲有一个稳定的先后顺序避免ROW_NUMBER在两个同样的次数之间随机分配编号。最后外层WHERE rn 3过滤前三并重新排序输出。你可能会有疑问为什么窗口函数不是直接写在原始表上而是先搞一个CTE因为播放次数是聚合结果窗口函数必须等GROUP BY执行完才能使用聚合值。如果在原始表上直接OVER(ORDER BY COUNT(*))语法上MySQL也能通过但语义上很难说清楚且一旦分组字段和窗口分区字段不一致结果会变得不可控。先聚合再开窗是更稳的写法。2.3 执行顺序窗口函数到底发生在哪一步整理一下MySQL 8.0的SQL执行顺序这对理解窗口函数至关重要FROM - WHERE - GROUP BY - HAVING - 窗口函数 - SELECT - DISTINCT - ORDER BY - LIMIT。窗口函数在GROUP BY和HAVING之后执行所以它能看到每个分组聚合后的结果这也是为什么在窗口函数里能引用play_cnt。同时窗口函数生成的列是在SELECT阶段才成为“列”的所以你绝不能在WHERE里写rn 3必须把开窗结果包一层子查询再过滤。很多人把窗口函数和WHERE搞混SQL一执行就报“Unknown column rn in where clause”原因就在这。记住这个顺序TopN题基本不会出错。3. 方案二MySQL 5.7用户变量没有窗口函数也能写3.1 为什么还要掌握老版本写法牛客网现在的在线环境基本都支持MySQL 8.0但面试官特别喜欢追问一句“如果数据库是5.7没有窗口函数你怎么实现”这时候只会CTE和OVER就尬住了。另一方面用户变量写法模拟的是“逐行扫描 状态记忆”的过程理解它之后你对SQL的面向集合思维会有更深的认识。所以我建议双方案都熟练掌握不是让你生产环境去写黑魔法而是为了在兼容场景和面试场景里都能稳一手。3.2 核心原理用两个变量记录“上一个月份”和“当前排名”用户变量写法的核心思路是把数据先按月份和播放次数排好序然后逐行扫描。维护两个变量cur_month记录上一行已经处理过的月份rn记录当前月份已经编号到几。扫描时每一行做一次判断如果这一行的months和cur_month相等说明还在同一个月份分组里rn自增1如果不等说明进入了新月份rn重置为1。判断结束后再把当前行的months更新到cur_month里供下一行使用。这就是当年没有窗口函数时老开发们常用的人工“游标”做法。注意一个非常容易踩的顺序问题必须先判断、后更新cur_month。如果把更新写在判断前面那当前月份永远等于cur_monthIF条件永远成立rn就会一直累加整张表只有一个月有排名后面所有月份也不会从1开始。这是80%的人写变量版TopN失败的原因。3.3 可复制的5.7标准模板与三个防坑点直接给一套在MySQL 5.7上验证过的模板SELECT months, song_name, play_cnt FROM ( SELECT rn : IF(cur_month months, rn 1, 1) AS rn, cur_month : months AS cur_month, months, song_name, play_cnt FROM ( SELECT months, song_name, play_cnt FROM ( SELECT DATE_FORMAT(p.play_time, %Y-%m) AS months, s.song_name AS song_name, COUNT(*) AS play_cnt FROM play_record p INNER JOIN song_info s ON p.song_id s.song_id WHERE s.singer_name 周杰伦 AND p.play_time 2021-01-01 00:00:00 AND p.play_time 2022-01-01 00:00:00 GROUP BY months, s.song_id, s.song_name ) a ORDER BY months, play_cnt DESC, song_name ASC ) sorted CROSS JOIN (SELECT rn : 0, cur_month : ) init ) r WHERE rn 3 ORDER BY months ASC, play_cnt DESC, song_name ASC;有三个防坑点必须说清楚。第一ORDER BY写在内层排序子查询sorted里要保证数据进入变量计算前已经是“月份有序、组内播放次数降序”的状态。如果遇到MySQL优化器把派生表ORDER BY吞掉的情况可以在sorted的ORDER BY后面加LIMIT 18446744073709551615强制保留排序但不建议滥用。第二变量初始化用CROSS JOIN子查询完成不要依赖MySQL把未声明变量默认为NULL的隐式行为。虽然第一行cur_month为NULL时IF判断也能让rn变成1但显式初始化更安心。第三和窗口函数一样外层ORDER BY必须显式说明月份、播放次数、歌曲名的排序规则不能指望变量计算时的临时顺序直接作为最终输出顺序。很多人在本机跑通一到牛客网就“答案一样但没分”往往就是少了这一步。4. 实操记录从建表到评测一次跑通4.1 建表与造数SQL纸上谈兵没用我把这道题在本机完整跑了一遍。先用下面语句建表CREATE TABLE song_info ( song_id VARCHAR(32) PRIMARY KEY, song_name VARCHAR(128) NOT NULL, singer_name VARCHAR(64) NOT NULL ); CREATE TABLE play_record ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, song_id VARCHAR(32) NOT NULL, play_time DATETIME NOT NULL, KEY idx_song_time (song_id, play_time), KEY idx_time_song (play_time, song_id) ); INSERT INTO song_info (song_id, song_name, singer_name) VALUES (j1, 晴天, 周杰伦), (j2, 七里香, 周杰伦), (j3, 稻香, 周杰伦), (j4, 夜曲, 周杰伦), (j5, Mojito, 周杰伦), (j6, 告白气球, 周杰伦), (o1, 孤勇者, 陈奕迅);play_record我造了三个月的播放记录保证每月都有结果。1月晴天5次、七里香4次、稻香3次、夜曲2次、Mojito1次。2月夜曲5次、七里香3次、稻香3次、晴天2次、Mojito1次、告白气球1次。3月七里香6次、稻香5次、晴天4次、夜曲3次、告白气球2次、Mojito1次。插入语句比较长直接贴出来INSERT INTO play_record (user_id, song_id, play_time) VALUES (1,j1,2021-01-01 10:00:00), (2,j1,2021-01-02 10:00:00), (3,j1,2021-01-03 10:00:00), (1,j1,2021-01-04 10:00:00), (2,j1,2021-01-05 10:00:00), (1,j2,2021-01-06 10:00:00), (2,j2,2021-01-07 10:00:00), (1,j2,2021-01-08 10:00:00), (3,j2,2021-01-09 10:00:00), (1,j3,2021-01-10 10:00:00), (2,j3,2021-01-11 10:00:00), (1,j3,2021-01-12 10:00:00), (1,j4,2021-01-13 10:00:00), (3,j4,2021-01-14 10:00:00), (1,j5,2021-01-15 10:00:00), (1,o1,2021-01-16 10:00:00), (1,j4,2021-02-01 10:00:00), (2,j4,2021-02-02 10:00:00), (1,j4,2021-02-03 10:00:00), (3,j4,2021-02-04 10:00:00), (2,j4,2021-02-05 10:00:00), (1,j2,2021-02-06 10:00:00), (2,j2,2021-02-07 10:00:00), (3,j2,2021-02-08 10:00:00), (1,j3,2021-02-09 10:00:00), (3,j3,2021-02-10 10:00:00), (1,j3,2021-02-11 10:00:00), (1,j1,2021-02-12 10:00:00), (2,j1,2021-02-13 10:00:00), (3,j5,2021-02-14 10:00:00), (1,j6,2021-02-15 10:00:00), (1,j2,2021-03-01 10:00:00), (2,j2,2021-03-02 10:00:00), (1,j2,2021-03-03 10:00:00), (3,j2,2021-03-04 10:00:00), (1,j2,2021-03-05 10:00:00), (2,j2,2021-03-06 10:00:00), (1,j3,2021-03-07 10:00:00), (2,j3,2021-03-08 10:00:00), (1,j3,2021-03-09 10:00:00), (3,j3,2021-03-10 10:00:00), (2,j3,2021-03-11 10:00:00), (1,j1,2021-03-12 10:00:00), (2,j1,2021-03-13 10:00:00), (1,j1,2021-03-14 10:00:00), (3,j1,2021-03-15 10:00:00), (1,j4,2021-03-16 10:00:00), (2,j4,2021-03-17 10:00:00), (3,j4,2021-03-18 10:00:00), (1,j6,2021-03-19 10:00:00), (2,j6,2021-03-20 10:00:00), (1,j5,2021-03-21 10:00:00);这里非周杰伦的o1虽然插入了但不会进入排名正好用来验证过滤条件是否生效。4.2 三种排名函数在同一份数据上的差异用上面造的数据跑窗口函数版本得到1月晴天5、七里香4、稻香32月夜曲5、七里香3、稻香33月七里香6、稻香5、晴天4。因为造数时刻意没做同月同次数的并列所以RANK和DENSE_RANK跑出来也和这个结果一致。为了演示差异你可以把1月晴天和七里香改成同样5次再看结果ROW_NUMBER按song_name补排序后输出晴天、七里香、稻香RANK会把晴天和七里香都排第1稻香排第3输出三行DENSE_RANK同样。如果再把稻香和夜曲都改成4次RANK和DENSE_RANK都会输出四行因为稻香和夜曲名次相同而ROW_NUMBER仍然只输出三行。这就是“并列要不要保留”的核心差异。实际业务宁可多展示并列歌曲也不要因为ROW_NUMBER扔掉用户数据所以业务系统我建议用DENSE_RANK刷题则看题目是否要求行数固定。4.3 牛客网在线评测的通过细节刷过牛客网的人都知道它比对的是查询结果集不看你SQL里有没有CTE、有没有窗口函数只看列名、顺序、数据和题目预期一不一致。我总结三个坑。第一输出列的顺序必须和题目示例一致比如题目要求“月份、歌曲名、播放次数”你写成“歌曲名、月份、播放次数”哪怕数据对也会判错。第二别名要用题目给的字段名而不是随便起一个temp。比如months就是months不要写成month或者m除非题目示例就是month。如果不确定尽量和题目输出表头保持一致。第三不要多带列比如把rn作为第四列输出会在比对时因为多一列直接不通过。另外时间过滤用 2021-01-01 AND 2022-01-01比用BETWEEN更严谨能避免2021-12-31 23:59:59之后的数据误查虽然BETWEEN在练习数据里看不出问题但这是值得养成的习惯。5. 常见问题与排查实录5.1 窗口函数报语法错误的排查路径MySQL 8.0版本如果报“Window function is not supported”可能不是语法问题而是你的MySQL实际是5.7或者云数据库老版本没开放。先执行SELECT VERSION();。如果确认是8.0还报错查一下是否在窗口函数里用了不支持的关键字比如PARTITION BY后面少了列名或者OVER()漏了括号。顺手写一个最小用例SELECT id, ROW_NUMBER() OVER (ORDER BY id) FROM play_record LIMIT 5;能跑说明环境没问题再去检查你自己的SQL。如果最小用例也报错就是版本或字符集问题。5.2 变量写法名次不重置、月份错乱用户变量版最常见的报错现象是1月rn到52月还在继续6、7、8完全没有从1开始。原因多半是变量赋值顺序错了把cur_month : months写在了rn : IF(...)前面导致每个月的判断永远命中“相等”分支。另一个现象是月份虽然是1、2、3但每个月的歌曲顺序忽高忽低这说明内层ORDER BY没有保住。修复方法检查最内层排序语句是否确实生效在数据量小的情况下也可以把变量计算子查询直接SELECT出来看看每行的rn和cur_month值基本一眼就能定位。5.3 为什么不能在WHERE里直接用rn 3前面说过窗口函数在WHERE之后执行这句话是判断这类报错的核心。如果你在SQL中写WHERE rn 3MySQL解析时发现rn根本不存在报Unknown column rn in where clause。正确做法永远是包一层子查询在外层过滤。CTE版本和我给的窗口函数版本都是先算rn再过滤就是这个原因。如果你不想写CTE也可以直接嵌套两层子查询效果一样。5.4 数据量大的时候两个方案的性能怎么取舍窗口函数在MySQL 8.0里处理“百万行播放记录”通常不是大问题但要注意它会为每个分区排序生成临时文件。如果2021年有12个月每个月几百万行建议把大表先做月份预聚合比如建一张play_month_cnt汇总表把明细数据预先按天/月累加这样查询时直接读汇总表窗口只排几十行速度会快很多。用户变量版本在5.7中也有类似问题它的原理是逐行扫描排序和变量赋值都会消耗临时表而且变量写法没法走索引加速只适合小数据量或面试手写。如果生产环境真的需要TopN报表我建议不要在SQL里硬扛可以把明细落到分析库里或者用定时任务预计算SQL只做最终展示。5.5 这个套路还能扩展到哪些场景分组TopN的套路不仅适用于歌曲每个部门工资最高的员工、每个商品的最近N条订单、每个用户最近登录时间本质上都是“按某个维度分区在组内排序再取前N”。你把题目里的months换成department_idsong_name换成employee_nameplay_cnt换成salarySQL主体结构不用动。窗口函数写顺手后这类需求基本是模板题反而要注意的是不要滥用如果N是动态的比如“取每个用户前10%的订单”就不能简单用rnN得配合COUNT(*) OVER()或PERCENT_RANK那就是另一层复杂度了。等Top3练熟了可以再往这些方向进阶。6. 一些实际刷题经验我在实际刷这道题时没有直接抄答案而是先把三种排名函数各自跑了一遍再用DeepSeek把我写的SQL和牛客网标准答案做了一次逐行对比。得出一个体会窗口函数的执行顺序是这类题的生命线只要记住“WHERE过滤的是原表行窗口函数发生在分组后”你就不容易写错。另外如果你本地有MySQL 8.0强烈建议把上面建表和造数SQL贴进去亲手验证一遍再故意把内层ORDER BY的song_name补排序删掉观察输出顺序会不会变化这种“故意破坏”比背题有用得多。最后一个小技巧在线评测如果总差一列多半是字段别名和输出顺序的问题把题目示例的输出表头复制过来逐个对齐列名基本能解决90%的“答案对了但没分”的情况。