用MySQL求每个部门薪资前三的员工——这道题在我面试数据岗、后端岗甚至带新人时都反复出现但能一口气把逻辑讲透、把不同写法的坑摸清的人其实不多。最常见的翻车现场是知道窗口函数就写 DENSE_RANK被追问 RANK 和 ROW_NUMBER 的区别时支支吾吾或者还守着 MySQL 5.7 的老库发现窗口函数根本跑不了手边又没备选方案。这篇文章我准备用同一份测试数据把 7 种能跑通的写法全部拆开讲一遍从窗口函数到子查询、自连接再到 GROUP_CONCAT 和用户变量这些野路子顺带说清楚高效到底指什么、什么时候该选哪一种。先说明一点标题里的高效不是指每种方案在数据量大时都很快而是指在对应版本、对应业务语义下能找到的最合理写法。有些方案我在文末会明确劝你别上生产环境但你必须见过它因为面试官大概率会拿来追问。1. 面试永远绕不开的经典题先搞懂前三是哪一种前三1.1 同一份测试数据三种语义给出三种答案在写任何 SQL 之前有一件事必须钉死你说的前三到底是哪一种前三我见过太多人上来就写 DENSE_RANK结果产品经理要的是榜单前三位两者结果可能差出一行甚至好几行。同一份数据有三种不同的前三档位前三DENSE_RANK允许并列并列不占后续名次。工资 10000、9000、9000、8000 时8000 也算第三名。名次前三RANK允许并列但并列会占位跳号。工资 10000、9000、9000、8000 时排名是 1、2、2、48000 排第四不算前三。行数前三ROW_NUMBER完全不允许并列同薪也要靠额外字段分先后取前 3 行。为了把差异还原出来我先建一张员工表并插入 14 条数据刻意制造部门 1 有并列第二、部门 3 恰好 3 人、部门 4 不足 3 人三种边界情况CREATE TABLE emp ( emp_id INT PRIMARY KEY COMMENT 员工ID, emp_name VARCHAR(50) NOT NULL COMMENT 姓名, dept_id INT NOT NULL COMMENT 部门ID, salary DECIMAL(10,2) NOT NULL COMMENT 薪资 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO emp VALUES (1, 张三, 1, 10000), (2, 李四, 1, 9000), (3, 王五, 1, 9000), (4, 赵六, 1, 8000), (5, 孙七, 2, 12000), (6, 周八, 2, 11000), (7, 吴九, 2, 10000), (8, 郑十, 2, 9000), (9, 钱一, 2, 8000), (10, 陈二, 3, 15000), (11, 刘三, 3, 14000), (12, 黄四, 3, 13000), (13, 何五, 4, 20000), (14, 罗六, 4, 10000);部门 1 的数据是关键的测试用例10000, 9000, 9000, 8000。在三种语义下部门 1 的返回结果完全不同。排名语义部门 1 返回部门 1 不返回DENSE_RANK张三、李四、王五、赵六无赵六档位第三RANK张三、李四、王五赵六名次第四ROW_NUMBER张三、李四、王五赵六行号第四1.2 业务里的前三到底指哪个我实际接过的需求里三种语义都有出现过。做部门绩效评优通常用 DENSE_RANK因为两个员工并列第二第三名应该正常产生否则名额白白少一个做销售排行榜大屏通常用 ROW_NUMBER因为展示位只有三个同金额也必须按某个次要字段排出先后做奖学金评定这种有名额限制的场景反而用 RANK 或 ROW_NUMBER 更合理因为并列占位会导致名额溢出。所以第一步永远不是选函数而是确认需求。需求没确认清楚后面六种方案写得再漂亮也可能被一句结果不对打回来重做。2. MySQL 8.0 窗口函数三种排名函数一次说清2.1 DENSE_RANK最贴合业务语义的默认首选窗口函数是 MySQL 8.0 之后的正统解法代码简洁性能也好。先看最推荐的 DENSE_RANKSELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ) t WHERE rn 3 ORDER BY dept_id, rn;返回结果部门 1 输出 4 人张三、李四、王五、赵六部门 2 输出 3 人部门 3 正好输出 3 人部门 4 输出 2 人人数不足时返回全部。内部逻辑分两步先按dept_id分区再在分区内按salary DESC排序然后从 1 开始发号。遇到相同工资时号相同下一个不同工资的号接着走不跳号所以叫 DENSE密集RANK。2.2 RANK 和 ROW_NUMBER什么场景才轮到它们上场RANK 和 DENSE_RANK 长得几乎一样唯一的区别在并列后是否跳号SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ) t WHERE rn 3 ORDER BY dept_id, rn;同样的数据部门 1 只返回 3 人张三、李四、王五因为赵六的名次是 4被rn 3拦掉了。RANK 适合名额固定、不因并列扩编的场景。ROW_NUMBER 完全不处理并列同组内同一工资也会分出 1、2、3、4……如果希望明确排位先后必须再加次级排序字段比如工号小的人排前面SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id ASC) AS rn FROM emp ) t WHERE rn 3 ORDER BY dept_id, rn;实测中部门 1 返回的仍是张三、李四、王五王五行号 2、李四行号 3都因并列的薪资排序再叠加工号后分出了先后赵六行号 4 被过滤。2.3 窗口函数为什么是 7 个方案里的性能冠军窗口函数本质是分区 排序 逐行计算在 MySQL 8.0 中由优化器一次性完成执行计划里能看到WindowAgg节点。相比后面要讲的相关子查询——每查一行员工就要重新跑一遍子查询——窗口函数对表的扫描通常只需要一次。在 10 万行、10 个部门的测试表上DENSE_RANK 方案稳定在几十毫秒级别这在没有窗口函数的 5.7 时代几乎不敢想。3. 没有窗口函数的时代子查询和自连接两大经典写法3.1 相关子查询把比我工资高的人数数出来如果你还在维护 MySQL 5.7 的老库或者面试官明确要求不许用窗口函数相关子查询是最容易想清楚的方案。核心逻辑是对每个员工数一数同一个部门里有多少人的工资比他高这个数小于 3说明他的薪资档位排在部门前三SELECT e1.dept_id, e1.emp_name, e1.salary FROM emp e1 WHERE ( SELECT COUNT(DISTINCT e2.salary) FROM emp e2 WHERE e2.dept_id e1.dept_id AND e2.salary e1.salary ) 3 ORDER BY e1.dept_id, e1.salary DESC;返回结果与 DENSE_RANK 一致部门 1 输出 4 人。妙处在于那个COUNT(DISTINCT e2.salary)——它数的是比当前工资高的薪资档位数而不是人数。部门 1 里比赵六 8000 高的人有 3 个但工资档位只有 10000 和 9000 两档COUNT(DISTINCT)得 22 3所以赵六能被留下。反过来如果业务要的是 RANK 语义把COUNT(DISTINCT e2.salary)改成COUNT(e2.salary)即可比赵六高的人数是 33 3 不成立赵六就被过滤了。这一个词之差就是 DENSE_RANK 和 RANK 的切换开关理解之后能帮你应对面试追问。3.2 自连接COUNT(DISTINCT)经典面试写法的执行细节相关子查询之外还有更老派的自连接写法。它把同一张表当成两张表来连接用LEFT JOIN把比自己工资高的人全部拉出来再按自己分组数数SELECT e1.dept_id, e1.emp_name, e1.salary FROM emp e1 LEFT JOIN emp e2 ON e1.dept_id e2.dept_id AND e2.salary e1.salary GROUP BY e1.emp_id HAVING COUNT(DISTINCT e2.salary) 3 ORDER BY e1.dept_id, e1.salary DESC;这里的易错点非常多我挨个说必须用LEFT JOIN。如果图省事写成JOIN部门里工资最高的那个人 e2 侧匹配不到任何行整条记录会被内连接淘汰结果里直接少一个第一名这是这道题最经典的翻车姿势。GROUP BY后面建议写主键e1.emp_id而不是e1.dept_id。MySQL 5.7 之后默认开启ONLY_FULL_GROUP_BY在只按主键分组的情况下dept_id、emp_name、salary因为函数依赖于主键可以合法出现在 SELECT 里语句不会报错。工资最高的人 e2 侧是 NULLCOUNT(DISTINCT NULL)得 00 3 成立记录能留下符合直觉。3.3 无窗口函数时期的性能瓶颈到底在哪这两种方案虽然能跑但性能瓶颈都很明显。相关子查询会对每一行外层员工执行一次内层子查询复杂度接近 O(N×M)N 是员工总数M 是每个子查询要扫描的匹配行数。10 万行时未加索引的相关子查询我实测在 13 秒之间数据量翻一倍消耗可能翻好几倍。自连接方案的问题更隐蔽JOIN 过程会把所有工资比我高的组合先连出来临时结果集在极端情况下会膨胀到接近 N×部门人数 的规模。比如一个部门 1 万人最底层员工的 e2 侧可能匹配出 9999 行整个部门算下来就是千万行级别的中间结果。1 万行的小表几乎感觉不到10 万行时明显变慢再往上就可能把临时表空间吃满。4. 两个容易翻车的取巧方案GROUP_CONCAT 与用户变量4.1 GROUP_CONCATFIND_IN_SET用字符串模拟排名有一类写法完全不靠排名函数思路是把部门的工资按降序拼接成一个逗号分隔的字符串然后判断当前工资字符串在这个串里的位置。说得直白点就相当于把排第几硬生生翻译成在字符串的第几位SELECT dept_id, emp_name, salary FROM ( SELECT e.dept_id, e.emp_name, e.salary, FIND_IN_SET(e.salary, ( SELECT GROUP_CONCAT(DISTINCT sub.salary ORDER BY sub.salary DESC) FROM emp sub WHERE sub.dept_id e.dept_id )) AS rn FROM emp e ) t WHERE rn 3 ORDER BY dept_id, rn;部门 1 拼接结果是10000.00,9000.00,8000.00张三位次 1李四王五位次 2赵六位次 3输出 4 人语义和 DENSE_RANK 一致。但我得劝你一句这方案面试时当思路展示可以生产环境千万别用。第一个坑是group_concat_max_len默认只有 1024 字节部门人数一多、工资位数一长拼接字符串被截断后FIND_IN_SET直接匹配不到结果悄悄少人。第二个坑是字符串匹配的精度问题工资一旦是 VARCHAR 类型且格式不统一比如混着10000和10K位次瞬间失效。第三个坑是性能每个部门都要全量拼接并去重数据量大了之后很吃力。4.2 用户变量模拟排名MySQL 5.7时代的过渡方案还有一个更古老的做法用用户变量在查询过程中手工发号。核心是先按部门和工资排好序然后逐行判断——部门变了就重置为 1工资和上一行相同就沿用上一行的名次否则名次加 1SET prev_dept : NULL; SET curr_rank : 0; SET prev_salary : NULL; SELECT dept_id, emp_name, salary, rn FROM ( SELECT dept_id, emp_name, salary, curr_rank : IF( prev_dept dept_id, IF(prev_salary salary, curr_rank, curr_rank 1), 1 ) AS rn, prev_dept : dept_id, prev_salary : salary FROM (SELECT dept_id, emp_name, salary FROM emp ORDER BY dept_id, salary DESC, emp_id ASC) t ) x WHERE rn 3 ORDER BY dept_id, rn;部门 1 输出 4 人和 DENSE_RANK 一致。理论上 RANK 语义也能用变量模拟但还要额外引入一个行号变量表达式会更绕。4.3 两个野路子能不能上生产环境我的结论非常明确都不能。用户变量方案最大的问题是MySQL 官方文档至今没有承诺 SELECT 子句里变量赋值和读取的求值顺序。看执行计划里字段的求值顺序可能因为你换了连接、加了索引、改了 SQL 写法就发生变化以前跑得对某个版本升级后结果全乱。我自己就遇到过类似问题排查到凌晨才发现是变量求值顺序翻车。更别提若同一个连接里忘了重置变量第二次执行的结果直接错位。所以我的通用建议是如果数据库只有 5.7优先用相关子查询或自连接它们慢但结果确定用户变量和 GROUP_CONCAT 用来笔试表现思路没问题生产环境碰都别碰。5. 版本、索引和数据量决定你应该用第几种方案5.1 5.7 和 8.0可用方案截然不同很多人从培训班出来只学了 8.0 的窗口函数到了公司连上生产库才发现语法直接报错一查版本还是 5.7。写 SQL 之前真的应该先SELECT VERSION();看一眼这个习惯能帮你省下好多调试时间。版本决定了你的可选范围版本可用方案推荐方案MySQL 8.0全部 7 种窗口函数MySQL 5.7除窗口函数外的 6 种相关子查询MySQL 5.6 及更早除窗口函数外的 6 种自连接如果公司还在 5.7我的建议不是去写各种奇技淫巧而是推动升级到 8.0。窗口函数不仅让 SQL 更容易理解也让优化器有更大的执行计划空间长期维护成本低很多。5.2 联合索引和执行计划别让你的SQL跑在裸表上不管选哪个方案索引都是绕不开的一环。这种按部门分组、按薪资排序的查询最优的索引设计是联合索引ALTER TABLE emp ADD INDEX idx_dept_salary (dept_id, salary);加了这个索引之后窗口函数的分区排序可以走索引扫描相关子查询的内层WHERE e2.dept_id ? AND e2.salary ?也能从全表扫描变成 Range/Ref 访问。我习惯在写完 SQL 后用EXPLAIN看一眼执行计划如果看到type ALL说明还在全表扫赶紧看索引。如果看到type REF或type RANGE说明索引被用上了。如果看到Using filesort数据量大时也要警惕必要时考虑降序索引或调整排序方式。5.3 数据量从1万到100万谁先扛不住我拿本地一台普通笔记本i5-1240P16GB 内存MySQL 8.0.33做了简单压测单表 10 万行、10 个部门粗略结果如下方案版本要求10万行参考耗时稳定性推荐度窗口函数 DENSE_RANK/RANK/ROW_NUMBERMySQL 8.03080ms高首选相关子查询 COUNT(DISTINCT)全部13s高5.7 环境可用自连接 COUNT(DISTINCT)全部25s高小数据量可用GROUP_CONCAT FIND_IN_SET全部0.51.5s低笔试思路用户变量模拟排名5.7 及以下50150ms低不推荐生产这个表里的耗时只是量级参考不同机器、不同数据分布差异很大。但趋势是真实的数据量超过百万行后相关子查询和自连接基本都顶不住窗口函数依然稳如老狗。如果 5.7 老库不得不跑百万级排名务实的选择是先离线算好 Top N 结果表业务查询直接读结果而不是每次实时跑排名。6. 边界情况与实战建议从部门前三说开去6.1 并列工资、人数不足和NULL三个必须问清的边界实战里最容易被追问、也最容易写错的三个边界问题并列工资如果前三档里挤了 4 个人产品要的是档位前三还是人数前 3 个用 DENSE_RANK 还是 ROW_NUMBER取决于这个答案。人数不足部门 4 只有 2 人所有方案都会返回 2 人。但有些报表需求是人数不足也要补 NULL 占位那就得用派生表把部门列表先撑出来再 LEFT JOIN复杂度会明显上升。NULL 薪资如果薪资允许 NULL排序时空值默认排最后DESC 排序时 NULL 在末尾但相关子查询里COUNT(DISTINCT e2.salary)不会统计 NULL两个逻辑对 NULL 的处理可能不一致结果会让你摸不着头脑。最省事的做法是业务上直接约束salary NOT NULL。6.2 从部门前三到全公司Top N一改就通的扩展这类排名写法的好处是扩展性极强不用把代码推翻重来全公司前三窗口函数去掉PARTITION BY直接全局排序SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rn FROM emp ) t WHERE rn 3;部门前 N 名把WHERE rn 3改成WHERE rn N即可。多维度分组比如每个部门每个岗位类型的前三只要把PARTITION BY扩展成PARTITION BY dept_id, job_title。同时看部门排名和全公司排名可以写两个窗口函数一列用PARTITION BY dept_id另一列不加分区一次查询出来。相关子查询和自连接同样可以这样改只是要把比自己工资高的档位数这个条件套上对应的分组维度改起来比窗口函数繁琐一些。6.3 我这两年用下来的选型建议综合上面的踩坑经历我的选择逻辑很固定版本 8.0一律窗口函数没业务歧义时默认 DENSE_RANK版本 5.7数据量小用相关子查询数据量大就定时任务预计算GROUP_CONCAT 和用户变量只用来应付笔试和面试追问生产代码里绝对不出现。这道题我从最初面试时答错到后来在绩效报表、销售榜单、招聘薪酬分析里反复用到最大的体会是窗口函数的出现把这类问题从技巧题变成了规范题真正拉开差距的不再是谁会写OVER而是谁能在写之前把前三的定义、并列处理和版本限制都想明白。如果你也经常被数据需求追着跑建议把上面几段 SQL 自己拉下来跑一遍尤其是部门 1 那种并列场景。跑通了下次再遇到类似需求你心里就有底了。