找出每个部门工资排名前 3 的员工。(分组取 top N)

考察要点

这道题是 SQL 面试中考察频率最高的题目之一，面试官想通过这道题考察你以下几点核心能力：

1. 窗口函数的熟练掌握：这是解决此类“分组 Top N”问题的标准、最高效的方案。面试官想看你是否知道并能熟练运用 RANK(), DENSE_RANK(), ROW_NUMBER() 等函数。
2. 对不同排名函数语义的精确理解：你能否清晰地解释 RANK(), DENSE_RANK(), ROW_NUMBER() 在处理薪资相等（并列）情况下的区别，并根据业务需求选择最合适的函数。
3. 复杂查询的逻辑构建能力：除了窗口函数，你是否还能想到其他解法（如子查询、自连接），这体现了你解决问题的广度和深度。

核心概念

解决本题的核心是窗口函数 (Window Functions)，特别是其中的排序函数 (Ranking Functions)。

窗口函数

窗口函数对一组与当前行相关的表行（这个集合被称为“窗口”）执行计算。它与聚合函数（如 GROUP BY）的主要区别在于：聚合函数会将多行聚合成一行，而窗口函数在计算后仍保持原始行数不变。

其基本语法为：

 1<窗口函数> OVER (
 2    [PARTITION BY <分组列>]
 3    ORDER BY <排序列>
 4    [<窗口大小子句>]
 5)

• PARTITION BY：将数据行分成多个“分区”或“小组”，窗口函数在每个分区内独立计算。对于本题，就是 PARTITION BY departmentId。
• ORDER BY：定义分区内行的排序方式，用于确定排名、前后等。对于本题，就是 ORDER BY salary DESC。

排序函数对比

假设我们有以下员工薪资数据（IT 部门）：

应用三个不同的排序函数，结果如下：

• ROW_NUMBER()：无论值是否相等，都分配一个连续的、唯一的排名。场景：需要严格取出 N 条记录，即使存在并列情况。
• RANK()：对于值相等的行，分配相同的排名，但下一个排名会跳过相应的位置。场景：并列排名，但总名额固定。
• DENSE_RANK()：对于值相等的行，分配相同的排名，且下一个排名是连续的。场景：找出排名前 N 的所有薪资水平，即使因为并列导致最终人数超过 N。

对于本题“排名前 3 的员工”，DENSE_RANK() 通常是最佳选择，因为它能找出所有位列前三薪资等级的员工。如果题目要求严格只要 3 个人，则 ROW_NUMBER() 更合适。

多种解法

准备数据 我们先定义并插入示例数据，以便后续查询。

 1-- DDL for Employee and Department tables
 2CREATE TABLE Department (
 3    id INT PRIMARY KEY,
 4    name VARCHAR(255) NOT NULL
 5);
 6
 7CREATE TABLE Employee (
 8    id INT PRIMARY KEY,
 9    name VARCHAR(255) NOT NULL,
10    salary INT NOT NULL,
11    departmentId INT,
12    FOREIGN KEY (departmentId) REFERENCES Department(id)
13);
14
15-- DML for inserting sample data
16INSERT INTO Department (id, name) VALUES (1, 'IT'), (2, 'Sales');
17
18INSERT INTO Employee (id, name, salary, departmentId) VALUES
19(1, 'Joe', 85000, 1),
20(2, 'Henry', 80000, 2),
21(3, 'Sam', 60000, 2),
22(4, 'Max', 90000, 1),
23(5, 'Janet', 69000, 1),
24(6, 'Randy', 85000, 1),
25(7, 'Will', 70000, 1);

解法 1：窗口函数（推荐，最优解）

使用 DENSE_RANK() 窗口函数，通过公用表表达式（CTE）使逻辑更清晰。

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 不支持窗口函数。
• SQL 代码：
  sql

  ⧉⛶

   1WITH RankedEmployees AS (
   2    SELECT
   3        e.name AS employee_name,
   4        e.salary,
   5        d.name AS department_name,
   6        DENSE_RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) as rnk
   7    FROM
   8        Employee e
   9    JOIN
  10        Department d ON e.departmentId = d.id
  11)
  12SELECT
  13    department_name,
  14    employee_name,
  15    salary
  16FROM
  17    RankedEmployees
  18WHERE
  19    rnk <= 3
  20ORDER BY
  21    department_name, salary DESC;

• 时间复杂度：主要开销在于 PARTITION BY 后的排序。如果数据总量为 N，分区数为 K，每个分区大小约为 N/K，则复杂度约为 $O(N \log(N/K))$。
• 索引依赖：在 Employee 表上建立 (departmentId, salary DESC) 的复合索引，可以极大地优化 PARTITION BY 和 ORDER BY 的性能，甚至可能避免内存排序，直接利用索引的有序性。

解法 2：关联子查询

这是一种更通用的、兼容老版本 MySQL 的解法，但性能通常较差。其核心思想是：对于每个员工，去计算他所在部门有多少人的薪水比他高。

• 版本：✅ 所有主流 SQL 数据库版本（包括 MySQL 5.7）。
• SQL 代码：
  sql

  ⧉⛶

   1SELECT
   2    d.name AS department_name,
   3    e1.name AS employee_name,
   4    e1.salary
   5FROM
   6    Employee e1
   7JOIN
   8    Department d ON e1.departmentId = d.id
   9WHERE (
  10    SELECT COUNT(DISTINCT e2.salary)
  11    FROM Employee e2
  12    WHERE e2.departmentId = e1.departmentId AND e2.salary > e1.salary
  13) < 3
  14ORDER BY
  15    d.name, e1.salary DESC;

  注：COUNT(DISTINCT e2.salary) 是为了正确处理薪资并列的情况，逻辑上等同于 DENSE_RANK()。如果用 COUNT(*)，则逻辑上等同于 RANK()。
• 时间复杂度：这是一个关联子查询，对于 e1 表的每一行，都会执行一次子查询。在没有合适索引的情况下，复杂度接近 $O(N^2)$，性能非常差。
• 索引依赖：即使在 Employee(departmentId, salary) 上有索引，其执行效率也远低于窗口函数。

常见低分回答

1. 误用 GROUP BY 和 LIMIT：

   sql

   ⧉⛶

    1-- 错误示范
    2SELECT d.name, e.name, e.salary
    3FROM Employee e JOIN Department d ON e.departmentId = d.id
    4GROUP BY e.departmentId
    5ORDER BY e.salary DESC
    6-- LIMIT 3; -- 即使加 LIMIT 也是错的

   错误原因：GROUP BY 会导致每个部门只返回一行，而返回哪一个员工的 name 和 salary 是不确定的（在标准 SQL 中非法，在 MySQL 中行为不可预测），完全偏离了题意。

2. 在 WHERE 子句中直接使用窗口函数：

   sql

   ⧉⛶

    1-- 语法错误
    2SELECT e.name, e.salary, d.name,
    3       DENSE_RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) as rnk
    4FROM Employee e JOIN Department d ON e.departmentId = d.id
    5WHERE rnk <= 3;

   错误原因：SQL 查询的逻辑执行顺序中，WHERE 子句在窗口函数之前执行。因此，在 WHERE 中无法引用由窗口函数生成的列别名 rnk。必须使用 CTE 或子查询。

3. 不理解 RANK 家族的区别：当面试官追问“如果薪资相同的人很多，你的查询会返回多少人？”时，如果候选人只知道 ROW_NUMBER()，或无法解释 RANK() 和 DENSE_RANK() 的区别，会显得基础不扎实。

4. 忘记处理部门分组：

   sql

   ⧉⛶

    1-- 逻辑错误
    2SELECT ..., DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM ...

   错误原因：OVER() 子句中缺少 PARTITION BY departmentId，导致排名是针对所有员工的全局排名，而不是部门内排名。

工程实践与优化

• 索引是关键：对于解法 1，在 Employee 表上创建 (departmentId, salary DESC) 的复合索引是性能优化的核心。数据库可以利用这个索引，避免对每个分区进行昂贵的“filesort”操作。
• EXPLAIN 分析：
  • 好的执行计划（使用窗口函数+索引）：EXPLAIN 结果中不应出现 Using filesort。在 Extra 列可能看到 Using index 或 Window func 相关信息。
  • 坏的执行计划（使用关联子查询）：EXPLAIN 结果中通常会看到 DEPENDENT SUBQUERY，这是性能杀手，应极力避免。
• 数据倾斜：如果某个部门的员工数量远超其他部门（例如，一个部门有 1000 万人，其他部门都只有几百人），窗口函数在处理这个超大分区时可能会消耗大量内存和 CPU。在这种极端情况下，可能需要将该超大部门的数据单独拿出来，用特殊逻辑（如分页、分批次）处理。
• 业务需求澄清：在写 SQL 之前，应与面试官（或产品经理）确认“前 3”的精确含义。是严格的 3 个人（ROW_NUMBER），还是薪资水平在前三等级的所有人（DENSE_RANK）？这体现了你的严谨性和沟通能力。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到亿级别，你的窗口函数 SQL 还能跑吗？怎么优化？

   • 回答要点 1 (索引与资源)：首先，必须确保 (departmentId, salary DESC) 索引存在且生效。亿级别数据量下，排序是主要瓶颈。如果内存不足以容纳一个大分区的排序，会产生磁盘临时文件，性能急剧下降。需要评估数据库服务器的内存和 CPU 配置。
   • 回答要点 2 (预计算)：对于报表或看板这类对实时性要求不高的场景，最佳实践是预计算。可以创建一个每日（或每小时）更新的物化视图或结果表，通过后台批处理任务（如 Spark、Flink 或简单的定时 SQL 脚本）计算好排名结果。前端查询直接读取这个结果表，实现毫秒级响应。

2. 追问：如果现在需求改成“找出每个部门薪水在部门整体薪水中排名前 20% 的员工”，该如何实现？

   • 回答要点：这考察的是其他窗口函数。可以使用 CUME_DIST() 或 PERCENT_RANK()。
     • CUME_DIST() (Cumulative Distribution)：计算一个值在一组值中的累积分布，结果是 (小于等于当前值的行数) / (分区总行数)。
     • PERCENT_RANK()：计算排名百分比，结果是 (rank - 1) / (分区总行数 - 1)。
   • 示例 SQL：
     sql

     ⧉⛶

      1WITH PercentileEmployees AS (
      2    SELECT
      3        ...,
      4        PERCENT_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) as percentile_rank
      5    FROM Employee
      6)
      7SELECT ... FROM PercentileEmployees WHERE percentile_rank < 0.2;

3. 追问：如果换成 PostgreSQL 或 Oracle，你的答案有什么不同？

   • 回答要点：
     • 语法兼容性：窗口函数是 SQL 标准的一部分，核心语法在 PostgreSQL, Oracle, SQL Server, MySQL 8.0+ 中基本一致。所以解法 1 的核心逻辑不需要改变。
     • 性能与优化器：不同数据库的查询优化器实现不同，对索引的利用、并行处理能力等方面有差异。例如，PostgreSQL 的窗口函数实现非常高效。Oracle 也有强大的分析函数功能。但根本的优化思路（使用窗口函数+正确索引）是通用的。
     • 特定功能：可能会有一些细微的语法糖或特定函数差异。例如，在处理 NULL 排序时，PostgreSQL 和 Oracle 支持 NULLS FIRST / NULLS LAST 语法，而 MySQL 需要用 CASE 语句模拟。但对于本题，这些差异不影响核心解法。