企业官网建设流程全解析

"用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、4，8000 排第四，不算前三。
• 行数前三（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 '薪资' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 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.2 业务里的"前三"到底指哪个

我实际接过的需求里，三种语义都有出现过。做"部门绩效评优"，通常用 DENSE_RANK，因为两个员工并列第二，第三名应该正常产生，否则名额白白少一个；做"销售排行榜大屏"，通常用 ROW_NUMBER，因为展示位只有三个，同金额也必须按某个次要字段排出先后；做"奖学金评定"这种有名额限制的场景，反而用 RANK 或 ROW_NUMBER 更合理，因为并列占位会导致名额溢出。

所以，第一步永远不是选函数，而是确认需求。需求没确认清楚，后面六种方案写得再漂亮，也可能被一句"结果不对"打回来重做。

2. MySQL 8.0 窗口函数：三种排名函数一次说清

2.1 DENSE_RANK：最贴合业务语义的默认首选

窗口函数是 MySQL 8.0 之后的正统解法，代码简洁，性能也好。先看最推荐的 DENSE_RANK：

SELECT 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)得 2，2 < 3，所以赵六能被留下。

反过来，如果业务要的是 RANK 语义，把COUNT(DISTINCT e2.salary)改成COUNT(e2.salary)即可：比赵六高的人数是 3，3 < 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;

这里的易错点非常多，我挨个说：

1. 必须用LEFT JOIN。如果图省事写成JOIN，部门里工资最高的那个人 e2 侧匹配不到任何行，整条记录会被内连接淘汰，结果里直接少一个第一名，这是这道题最经典的翻车姿势。
2. GROUP BY后面建议写主键e1.emp_id而不是e1.dept_id。MySQL 5.7 之后默认开启ONLY_FULL_GROUP_BY，在只按主键分组的情况下，dept_id、emp_name、salary因为函数依赖于主键，可以合法出现在 SELECT 里，语句不会报错。
3. 工资最高的人 e2 侧是 NULL，COUNT(DISTINCT NULL)得 0，0 < 3 成立，记录能留下，符合直觉。

3.3 无窗口函数时期的性能瓶颈到底在哪

这两种方案虽然能跑，但性能瓶颈都很明显。

相关子查询会对每一行外层员工执行一次内层子查询，复杂度接近 O(N×M)，N 是员工总数，M 是每个子查询要扫描的匹配行数。10 万行时未加索引的相关子查询，我实测在 1～3 秒之间，数据量翻一倍，消耗可能翻好几倍。

自连接方案的问题更隐蔽：JOIN 过程会把所有"工资比我高"的组合先连出来，临时结果集在极端情况下会膨胀到接近 N×部门人数 的规模。比如一个部门 1 万人，最底层员工的 e2 侧可能匹配出 9999 行，整个部门算下来就是千万行级别的中间结果。1 万行的小表几乎感觉不到，10 万行时明显变慢，再往上就可能把临时表空间吃满。

4. 两个容易翻车的取巧方案：GROUP_CONCAT 与用户变量

4.1 GROUP_CONCAT+FIND_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，工资和上一行相同就沿用上一行的名次，否则名次加 1：

SET @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();看一眼，这个习惯能帮你省下好多调试时间。

版本决定了你的可选范围：

如果公司还在 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-1240P，16GB 内存，MySQL 8.0.33）做了简单压测，单表 10 万行、10 个部门，粗略结果如下：

这个表里的耗时只是量级参考，不同机器、不同数据分布差异很大。但趋势是真实的：数据量超过百万行后，相关子查询和自连接基本都顶不住，窗口函数依然稳如老狗。如果 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 那种并列场景。跑通了，下次再遇到类似需求，你心里就有底了。