MySQL分组Top N的7种写法：窗口函数与5.7兼容方案全解析

先问个问题如果领导让你出一张报表统计每个部门工资最高的三个人你多长时间能写出来很多人的第一反应是LIMIT 3但再想想就发现不对——LIMIT是对整个结果集取前 3 行根本没法按部门分组。这个场景在 SQL 里有个专门的说法叫“分组 Top N”也是 MySQL 面试题里出现频率最高的题型之一。我拿这个问题面过不少人也在真实报表里改过不少低效写法今天干脆把这个问题掰开揉碎把我实际验证过的 7 种方案全部整理出来从窗口函数到老版本兼容写法该说的坑一个不落。先说结论方向MySQL 8.0 和 5.7 的解法完全是两套思路8.0 用窗口函数三下五除二5.7 得靠子查询、自连接或者用户变量绕路。这篇文章不仅会给出每种方案的 SQL还会解释每条 SQL 背后的执行逻辑、并列工资的处理差异、索引怎么建以及生产环境里最容易踩的坑。适合正在准备面试的人也适合被报表 SQL 折磨过的开发同事。1. 问题定义与测试数据准备1.1 业务需求到底在问什么“部门前三薪资排名”这句话看着简单但落地到 SQL 之前必须先确认三件事。第一业务上说的“前三”到底是指“工资最高的前三个薪资档位”还是“严格取 3 个员工”这两个语义在工资有并列时结果完全不同。第二同一个部门里工资相同的人怎么处理两人并列第一、都算前三吗第三如果一个部门没有任何员工报表里需不需要给这个部门补一行空数据这三个问题不确认清楚SQL 写出来你都没法说它对还是错。比如部门里两个人都是 15000第三名工资 12000按“前三名”算到底是两分钟卖个关子还是三个人都算不同方案给出的答案是不一样的。后面每一个方案我都会说明它对应哪种语义。1.2 建表、造数、建立索引为了把话说清楚我们先建一张员工表故意制造两个高薪并列的场景后面对比方案时直接看数据。CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL, dept_id INT NOT NULL, salary DECIMAL(10, 2) NOT NULL, KEY idx_dept_salary (dept_id, salary) );这里先解释一下索引设计(dept_id, salary)这个复合索引是整个题目的关键几乎所有方案能不能跑得快都取决于它。部门字段做等值过滤工资字段做排序和比较这两个字段组成联合索引既能加速WHERE条件的过滤也能让ORDER BY salary DESC直接利用索引顺序尽量避免 filesort 和临时表。插入测试数据我特地让部门 1 出现两个 15000 的并列部门 3 出现两个 11000 的并列INSERT INTO emp (name, dept_id, salary) VALUES (张伟, 1, 15000), (李娜, 1, 15000), (王强, 1, 12000), (赵磊, 1, 10000), (钱进, 1, 9000), (孙悦, 2, 18000), (周敏, 2, 17000), (吴昊, 2, 16000), (郑爽, 2, 8000), (冯军, 2, 7000), (陈静, 3, 12000), (褚健, 3, 11000), (卫芳, 3, 11000), (蒋明, 3, 10000), (沈飞, 3, 9500);1.3 三种窗口函数的名次预览后面七个方案里有三个都基于窗口函数。为了让你一眼看出它们仨的区别先跑一条 SQL 同时输出ROW_NUMBER、RANK、DENSE_RANK三种排名SELECT dept_id, name, salary, ROW_NUMBER() OVER w AS rn_phys, RANK() OVER w AS rk, DENSE_RANK() OVER w AS drk FROM emp WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);这条 SQL 里用到的WINDOW w AS是 MySQL 8.0 新加的窗口定义语法作用是把相同的PARTITION BY和ORDER BY抽出来复用不用在三个OVER里重复写三遍属于能提升可读性的小技巧。部门 1 的数据会尤其有意思15000 和 15000 并列第一12000 在RANK里排第 3、在DENSE_RANK里排第 210000 在RANK里排第 4、在DENSE_RANK里排第 3。这意味着如果按DENSE_RANK取前 3 档工资 10000 的赵磊也会被选进来结果变成 4 个人。很多人在这一步就翻车了后面详细拆。2. 七种方案SQL 与原理逐项拆解2.1 方案一ROW_NUMBER 严格取前 3 个人第一种方案用ROW_NUMBER语义最明确就是“物理上按工资从高到低排取前 3 行”。SELECT dept_id, name, salary FROM ( SELECT dept_id, name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ) t WHERE rn 3 ORDER BY dept_id, salary DESC;执行逻辑分三层第一步按dept_id分区也就是把同一部门的员工分到一组第二步在每个分区内按salary DESC排序从高到低第三步用ROW_NUMBER()给排好序的行依次编号1、2、3……最后一层查询过滤出编号小于等于 3 的行。这套方案适合业务明确要求“一定只取 3 个人”的场景哪怕三个人里有两个工资一样也必须选出两个不同的人。但这里有个隐藏风险如果并列工资有很多人比如部门里 5 个人都是 15000那么到底选哪两个人进前三取决于 MySQL 的排序稳定性和索引顺序结果可能变得不可控。生产环境里如果遇到这种需求我通常会在ORDER BY里再加一个确定性的辅助排序字段比如入职日期hire_date ASC让“工资相同的时候入职早的人排在前面”成为一种显式规则而不是交给数据库自由发挥。2.2 方案二RANK 名次并列且跳号RANK的逻辑是相同工资给相同名次下一个不同工资的名次等于“前面的人数加一”所以名次会跳号。SELECT dept_id, name, salary FROM ( SELECT dept_id, name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM emp ) t WHERE rk 3 ORDER BY dept_id, salary DESC;用测试数据来看部门 1 中张伟和李娜都是 15000并列第 1王强 12000 排第 3赵磊 10000 排第 4。WHERE rk 3最终选出张伟、李娜、王强三个人。部门 3 中陈静 12000 排第 1褚健和卫芳都是 11000 并列第 2蒋明 10000 排第 4所以rk 3也只选三人。这个方案的业务语义是“名次小于等于 3 的员工全部显示”。如果部门里有四个 15000那么这四个人的名次都是 1工资 12000 的人名次是 5rk 3的结果就是四个人——因为四个人的名次都小于等于 3这是完全符合“取前三名”这个说法的。所以用RANK时要心里有数它允许并列而且允许结果超过 3 行。2.3 方案三DENSE_RANK 按工资档位取前三档DENSE_RANK和RANK的区别在于不跳号相同工资给相同名次下一个不同工资的名次直接加 1不关心前面有几个人。SELECT dept_id, name, salary FROM ( SELECT dept_id, name, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drk FROM emp ) t WHERE drk 3 ORDER BY dept_id, salary DESC;关键差异在部门 1张伟、李娜并列第 1王强 12000 排第 2赵磊 10000 排第 3。drk 3会把赵磊也选进来部门 1 的结果变成四个人。为什么会这样因为DENSE_RANK本质是按“工资档位”排名它选的是“前三个工资档位排名不超过 3 的所有人”。当第一名有两个人并时原来的第三名会被顺延成第二档第四名被顺延成第三档于是第四名也能进结果。这在薪酬分析里其实很合理如果工资只有三个档位那每个档位里的人都应该出现在结果中不应该因为并列人数多就让后面档位的人被挤出名单。三个窗口函数放一起对比差异一眼就能看清方案名次是否并列是否跳号部门 1 取前 3 的结果人数业务语义ROW_NUMBER否无并列概念3 人严格取 3 个员工RANK是是3 人名次 ≤ 3 的员工DENSE_RANK是否4 人含 10000 档前 3 档工资的所有人这里多说一句这个差异是我在真实报表里踩过的同事用DENSE_RANK出了“部门前三”结果一个部门出现 6 个人业务方一脸懵。后来核对需求才发现业务方想要的是“严格 3 人”得用ROW_NUMBER。所以拿到需求先问清楚并列怎么处理再选函数。2.4 方案四相关子查询 COUNT(DISTINCT)如果数据库还是 MySQL 5.7没有窗口函数那就要用传统写法。方案四是一个相关子查询面试里非常常见。SELECT e.dept_id, e.name, e.salary FROM emp e WHERE ( SELECT COUNT(DISTINCT e2.salary) FROM emp e2 WHERE e2.dept_id e.dept_id AND e2.salary e.salary ) 3 ORDER BY e.dept_id, e.salary DESC;逐行解释一下它为什么能选出前三档。对emp e里的每一行子查询会统计“同一部门中工资严格大于当前行工资 e.salary 的工资档位数”。如果这个数字小于 3说明当前工资能排进该部门的前三档。比如部门 1 里工资 12000 的王强比他高的工资只有 15000 这一档COUNT(DISTINCT)结果是 1小于 3所以入选。工资 10000 的赵磊比他高的工资有 15000 和 12000 两档数字是 2也小于 3所以也会入选。这其实就是DENSE_RANK的语义——按薪资档位取前 3 档。COUNT(DISTINCT e2.salary)里的DISTINCT是灵魂。如果去掉它改写成COUNT(*)两个 15000 的员工会各自被计一次语义就变成了“比自己工资高的人数小于 3”在并列情况下结果就可能错乱。所以面试时如果被问到这个方案一定要能解释清楚这个细节。性能上要提醒一句这个方案外层是对整张emp表遍历每一行都执行一次子查询数据量大时开销不小。好在子查询条件是e2.dept_id e.dept_id AND e2.salary e.salary正好命中我们前面建的(dept_id, salary)复合索引每个子查询都能快速定位小数据量下完全够用笔试面试更是加分项。2.5 方案五自连接 GROUP BY HAVING方案五和方案四本质是同一个思路只是把“子查询”换成了“自连接”。有些人习惯叫它“表关联 分组过滤法”。SELECT e.dept_id, e.name, e.salary FROM emp e LEFT JOIN emp e2 ON e2.dept_id e.dept_id AND e2.salary e.salary GROUP BY e.id HAVING COUNT(DISTINCT e2.salary) 3 ORDER BY e.dept_id, e.salary DESC;执行过程是这样emp e是当前的员工emp e2是同一部门里工资严格高于当前员工的其他人。两表连接后当前员工的每一行会跟多个“比自己高薪”的记录横向拼接然后GROUP BY e.id把所有比自己高的人聚合成一组最后HAVING COUNT(DISTINCT e2.salary) 3判断比自己高的工资档位数是否少于 3。这里有两个细节特别容易出错。第一个细节是必须用LEFT JOIN而不是INNER JOIN。INNER JOIN会在连接时把“没有更高薪记录”的员工过滤掉而这个“没有更高薪记录的人”恰恰是每个部门的最高薪员工是 Top 3 里必须保留的人。只有LEFT JOIN才能让最高薪员工在连接后保留下来因为右边e2匹配不到行时会是NULLCOUNT(DISTINCT NULL)结果是 0仍然小于 3。第二个细节是GROUP BY e.id的写法。MySQL 5.7.5 之后默认开启ONLY_FULL_GROUP_BY模式如果你写成GROUP BY e.name, e.salary, e.dept_id也行但那样语句很啰嗦。按主键GROUP BY e.id是更聪明的写法因为 MySQL 支持“函数依赖检测”当分组列包含主键时SELECT 中该表的其他字段可以放心地出现在查询列表里不会报ONLY_FULL_GROUP_BY错误。这是一个很实用的小技巧建议背下来。方案五和方案四在优化器眼里可能被改写成等价的形式但自连接理论上会先生成中间连接结果单部门人数特别多时中间结果集可能膨胀得比较厉害。如果数据量不大怎么选都无所谓数据量大建议优先看执行计划再决定。2.6 方案六GROUP_CONCAT FIND_IN_SET这个方案算是一个偏门技巧但用好了也很有意思。思路是把每个部门所有不重复的工资从高到低拼成一个逗号分隔的字符串然后用FIND_IN_SET判断当前员工的工资在这个字符串里排第几位位置在 1 到 3 之间就说明属于前三档。SELECT e.dept_id, e.name, e.salary FROM emp e WHERE FIND_IN_SET( e.salary, (SELECT GROUP_CONCAT(DISTINCT e2.salary ORDER BY e2.salary DESC) FROM emp e2 WHERE e2.dept_id e.dept_id) ) BETWEEN 1 AND 3 ORDER BY e.dept_id, e.salary DESC;以部门 1 为例子查询会生成字符串15000,12000,10000,9000。张伟的工资 15000 在字符串里位置是 1王强的 12000 位置是 2赵磊的 10000 位置是 3所以这四个人都会被选中。这个方案天然就是“按不重复薪资档位取前三档”的语义和DENSE_RANK一致。这个方案最大的优点是可以利用子查询和索引快速拿到一个很短的工资档位字符串当公司薪资档位很少时性能可观。但坑也不少一是GROUP_CONCAT默认最大长度是 1024 字节如果某个部门工资档位特别多拼接结果会被静默截断出现漏人甚至查不到数据的诡异现象。解决办法是在查询前执行SET SESSION group_concat_max_len 1048576;或者直接避免在核心生产查询里用这个方案。二是FIND_IN_SET是把数值当字符串处理如果工资字段是DECIMAL类型可能出现精度转换问题所以如果要玩这个方案建议先转成整数或者确认数据是整数。三是如果某个员工的工资是NULLGROUP_CONCAT会直接忽略这个人永远进不了前三。这个方案我一般定位是“面试时展示 SQL 功底”或者“一次性临时报表”时使用生产环境不推荐作为长期方案。核心原因就是它依赖group_concat_max_len这个会话变量一旦数据增长稳定性就成问题。2.7 方案七用户变量模拟行号老版本救星最后这套方案是专为 MySQL 5.7 及以下版本准备的。没有窗口函数但可以用“用户变量”手写一个行号生成器。SELECT dept_id, name, salary FROM ( SELECT e.dept_id, e.name, e.salary, rn : IF(prev_dept e.dept_id, rn 1, 1) AS rn, prev_dept : e.dept_id FROM emp e CROSS JOIN (SELECT rn : 0, prev_dept : NULL) vars ORDER BY e.dept_id, e.salary DESC LIMIT 100000000 ) t WHERE rn 3 ORDER BY dept_id, salary DESC;分几步理解。先看CROSS JOIN (SELECT rn : 0, prev_dept : NULL) vars这行代码的作用是初始化两个用户变量把它们当作“临时内存变量”用。rn表示当前行号prev_dept用来记住上一行是哪个部门。主查询按dept_id, salary DESC排序后逐行扫描如果prev_dept等于当前行的部门说明还在同一个部门内行号rn加 1如果部门变了行号重新置为 1。这个逻辑就是手动实现了ROW_NUMBER()。这个方案在 5.7 时代性能相当好整个流程只需要一次全表扫描加一次排序比相关子查询动不动 N 次子查询要划算得多。但有两个版本相关的坑必须说清楚。第一个坑是派生子查询里的ORDER BY在 MySQL 8.0.29 之前可能会被优化器当作无用操作直接丢掉从而导致排序失效。业内常用的土办法就是我在 SQL 里加的那个LIMIT 100000000它让优化器认为这个排序是有意义的不能随便删。第二个坑更严重MySQL 官方文档明确指出用户变量在同一个 SQL 语句里的求值顺序是不保证的。也就是说rn : IF(...)和prev_dept : e.dept_id这两个赋值操作谁先谁后在不同版本、不同执行计划下可能不一样结果就会出现错乱。所以这个方案只建议在 5.7 及以下使用如果项目已经升到 8.0直接换成窗口函数别在这里纠结。3. 性能对比与索引优化建议3.1 执行计划视角每个方案的访问路径七种方案都摆出来了接下来必须回答那个关键问题到底哪个“高效”先给结论框架再逐一说理由。方案核心写法是否依赖 8.0有索引时的扫描特征并列语义方案一ROW_NUMBER是一次扫描 一次排序严格前 3 人方案二RANK是一次扫描 一次排序名次 ≤ 3方案三DENSE_RANK是一次扫描 一次排序前 3 档工资方案四相关子查询否外层全表 N 次子查询前 3 档工资方案五自连接 GROUP BY否连接 分组前 3 档工资方案六GROUP_CONCAT否外层全表 N 次子查询前 3 档工资方案七用户变量否8.0 有风险一次扫描 一次排序严格前 3 人窗口函数方案在数据访问上是最优的它把“分区”和“排序”交给优化器统一处理理想情况下一次扫描就能完成。配合上(dept_id, salary)索引排序可以直接走索引顺序连 filesort 都能省掉。相关子查询最吃亏的地方是外层有多少行子查询就执行多少次严格说是 O(N) 次子查询N 是员工总数所以它的执行成本跟表大小线性挂钩。自连接在数据量大的时候会生成较大的中间连接结果尤其在单部门人数膨胀时中间结果集可能让临时表压力很大。用户变量方案则是“一次扫描 一次排序”在老版本里几乎没有对手。3.2 复合索引为什么必须按 (dept_id, salary) 设计索引设计是这个题目的隐藏考点。(dept_id, salary)这个复合索引首先能支撑WHERE dept_id ?这样的等值过滤其次能让ORDER BY salary DESC在分区内有序。MySQL 的复合索引遵循最左前缀原则查询条件里只要出现了dept_id就能用上这个索引如果只单独查salary这个索引就用不上了。如果 SELECT 里还需要回表取name字段那更好的做法是建一个覆盖索引(dept_id, salary, name)。这样查询过程中不需要回原表直接从索引里就能拿到所有需要的数据执行计划里会出现Using index性能又会提升一个档次。需要注意的一个点是ORDER BY salary DESC和索引默认的升序一致虽然 MySQL 8.0 之后也支持降序索引但多数场景下这个排序并不需要特意建降序索引优化器可以通过反向扫描来支持降序排序。除非你的查询既有升序又有降序的混合排序需求否则不用过度设计。3.3 大数据量下的方案选型结论根据实际场景来选不同条件有不同最优解。如果数据库是 MySQL 8.0优先窗口函数。要“严格 3 人”用ROW_NUMBER要“前三名含并列”用RANK要“前三档工资”用DENSE_RANK。如果数据库是 5.7数据量大且对性能敏感可以用用户变量方案加LIMIT 100000000兜底排序数据量小相关子查询方案更简单可靠也好解释给别人听。如果薪资档位很少且数据量不大GROUP_CONCAT FIND_IN_SET可以玩一下但要记得调大group_concat_max_len并且确认工资字段没有精度问题。如果公司已经上了 8.0但代码里还遗留着用户变量写法不要犹豫赶紧改成窗口函数。用户变量在 8.0 里不仅没有性能优势还可能有求值顺序的坑。4. 实战避坑并列、空部门、老版本兼容问题4.1 并列工资到底选谁这个问题本质上是需求评审问题不是 SQL 语法问题。我建议把下面这张表打印出来跟业务方确认一下比写完代码再改要快得多业务原话方案“只要 3 个人”ROW_NUMBER“前三名都显示并列都算”RANK“按工资档位前三档都显示”DENSE_RANK“不能写窗口函数的老库”相关子查询 / 用户变量真实场景里最常被误解的是DENSE_RANK。我见过不止一次业务方说“取每个部门薪资前三档”开发人员用ROW_NUMBER硬取 3 人结果一个部门全员工资都很低、大家都靠同一档工资时数据看着完全不对。用一句话来记RANK看名次DENSE_RANK看档位ROW_NUMBER看物理行。4.2 空部门要不要显示如果业务要求“每个部门都要有结果没人的部门也要显示”那光靠emp表是不行的因为emp表里根本没有空部门的记录。正确做法是把部门表作为驱动表用LEFT JOIN关联员工再套上排名逻辑。SELECT d.dept_id, e.name, COALESCE(e.salary, 0) AS salary FROM department d LEFT JOIN emp e ON e.dept_id d.dept_id LEFT JOIN ( SELECT dept_id, name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ) r ON r.dept_id d.dept_id AND r.name e.name AND r.rn 3;这里要特别注意如果外层加了WHERE r.rn 3之类的过滤条件LEFT JOIN就会被悄悄变成INNER JOIN空部门的记录又在过滤时被干掉。想保留空部门过滤条件要么写进ON子句里要么写在外面时同时判断r.rn IS NULL。4.3 五个容易翻车的细节第一个坑是ONLY_FULL_GROUP_BY。方案五如果写成GROUP BY e.id没问题但如果你把人名、部门、工资三个字段都写进 SELECT 却只GROUP BY e.salaryMySQL 会直接报错。记住要么分组列包含主键要么把 SELECT 的所有非聚合字段都加进 GROUP BY。第二个坑是GROUP_CONCAT截断。默认 1024 字节生产数据一多很容易触发。遇到结果突然少人的情况先执行SHOW SESSION VARIABLES LIKE group_concat_max_len;确认一下长度。第三个坑是用户变量在 8.0 里的不稳定性。MySQL 8.0.29 之后对派生表ORDER BY的处理方式变了用户变量方案的结果可能直接乱掉。遇到这种问题不要再调试变量赋值顺序直接换窗口函数。第四个坑是字段类型。工资字段建议用DECIMAL不要用FLOAT或DOUBLE浮点数在比较时会有精度误差可能导致排名边界值算错而且FIND_IN_SET这类字符串函数在处理浮点字段时更加不可控。第五个坑是子查询返回多行。方案四、方案五中如果子查询里少写了e2.dept_id e.dept_id这个关联条件COUNT(DISTINCT e2.salary)统计的就是全表工资档位结果会完全错乱。写相关子查询时先查一遍子查询单独执行的结果确认返回的数字和预期一致再接上外层过滤。4.4 面试延伸一道题搞定一类分组 Top N面试官一旦看到你能把“部门前三”写出多种方案大概率会趁热打铁继续追问几个变体。我列几个最常见的求每个部门工资第二高的员工窗口函数写法只要把rn条件从 3 改成 2SELECT dept_id, name, salary FROM ( SELECT dept_id, name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ) t WHERE rn 2;取每个用户最近一次登录记录本质也是分组 Top N只要把排序字段换成login_time DESCSELECT user_id, login_time, device FROM ( SELECT user_id, login_time, device, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS rn FROM login_log ) t WHERE rn 1;连续登录天数、滑动平均、同比环比这类问题底层也离不开窗口函数的PARTITION BY和ORDER BY。所以掌握了“分区 排序 过滤序号”这一套逻辑一个知识点能打通一类面试题。最后说点我自己的经验。拿到这种需求第一件事不是写 SQL而是问清楚MySQL 是哪个版本并列工资怎么算是要 3 个人还是 3 档工资这三个问题问完答案基本已经出来了。写完之后也别急着交差花十秒钟看一眼EXPLAIN确认是不是走了(dept_id, salary)这条索引往往比纠结选哪个方案更关键。如果你在旧项目里不得不用用户变量方案记得给代码留个注释标注“升级到 8.0 后请替换为 ROW_NUMBER”等 MySQL 版本一升级这段代码就能少一个坑。