第 12.4 课 · 阅读时间：约 7 分钟

“找出第二高的薪水”是 SQL 面试中最常见的问题之一，通常还会追问“那第 N 高呢”和“每个部门的呢”。在本课中，你将用五种方法解决它：MAX 加子查询、LIMIT ... OFFSET、DENSE_RANK() 以及相关子查询，并了解面试官最关注的陷阱：相同的薪水、不存在第 N 个值，以及分组。

SQL 第 N 高的薪水

这道题看似简单，也正因如此才广为流行：第一次尝试通常在示例数据上能跑通，却在边界情况上出错。一个好的答案要处理三件事：并列（两名员工薪水相同）、无结果（不同的薪水少于 N 个）以及分组（每个部门的第 N 高薪水）。

示例使用 MySQL 8.0 语法，并会创建自己的表，因此请在练习场中选择不含示例数据的数据库来运行。

示例数据

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(30) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employee VALUES
    (1, 'Alice', 'Sales', 5000),
    (2, 'Bob', 'Sales', 7000),
    (3, 'Carol', 'Sales', 7000),
    (4, 'Dave', 'IT', 9000),
    (5, 'Eve', 'IT', 6500),
    (6, 'Frank', 'IT', 9000),
    (7, 'Grace', 'HR', 4800),
    (8, 'Heidi', 'HR', 5200);

从高到低的不同薪水为 9000、7000、6500、5200、5000、4800。有两人拿 9000，两人拿 7000，因此第二高的薪水是 7000，而不是 9000。

解法 1：小于最大值的最大值

第二高的薪水，就是小于最高薪水的薪水中最大的那个：

SELECT MAX(salary) AS second_highest_salary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

结果：7000。

这种写法很短，适用于所有数据库，能正确处理并列，并且在没有第二高薪水时返回 NULL，因为空集合的 MAX 就是 NULL。它的局限是：只适用于 N = 2。要求第三高的薪水，就需要再嵌套一层。

解法 2：DISTINCT 加 LIMIT 和 OFFSET

把薪水从高到低排序，跳过前 N − 1 个：

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

结果：7000。

这里 DISTINCT 必不可少。没有它，排序后的列表是 9000、9000、7000、……，OFFSET 1 会落在第二个 9000 上：

-- 错误：由于并列，又返回了最高薪水
SELECT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

结果：9000，即最高薪水，而不是第二高。

没有结果时返回 NULL

如果 N 大于不同薪水的个数，查询会返回零行。面试题通常要求此时返回 NULL。把查询包在标量子查询中即可：找不到任何行的子查询会返回 NULL。

SELECT (
    SELECT DISTINCT salary
    FROM employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 9
) AS tenth_highest_salary;

结果：一行 NULL；不同的薪水只有 6 个。

LIMIT ... OFFSET 是 MySQL、PostgreSQL 和 SQLite 的语法。SQL Server 和 Oracle 12c+ 使用 OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY。

解法 3：DENSE_RANK

窗口函数为每一行排名，外层查询选出需要的名次。这是最灵活的解法，也是面试官通常期待的答案：

WITH ranked AS (
    SELECT name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employee
)
SELECT name, salary
FROM ranked
WHERE salary_rank = 2;

结果：Bob 和 Carol，薪水都是 7000。

它还能回答接下来的问题——“谁拿第二高的薪水”。把 2 换成任意 N 即可。这里只有 DENSE_RANK 是正确的：

ROW_NUMBER 给相同的薪水不同的编号，RANK 在并列之后会跳号，所以 RANK = 2 找不到任何人。DENSE_RANK 对不同的值连续编号，不会跳号。

解法 4：相关子查询

在窗口函数出现之前，经典答案是：如果恰好有 N 个不同的薪水大于或等于某个薪水，那它就是第 N 高的薪水。

SELECT DISTINCT e1.salary
FROM employee e1
WHERE 3 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employee e2
    WHERE e2.salary >= e1.salary
);

结果：6500，即第三高的薪水。

这种写法适用于任何数据库，但子查询会对每一行执行一次，因此在大表上很慢。可以把它作为备选方案提一下，实际工作中请使用 DENSE_RANK。

解法 5：每个分组中的第 N 高

常见的追问是：“每个部门第二高的薪水”。在窗口函数中加上 PARTITION BY：

WITH ranked AS (
    SELECT department, name, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM employee
)
SELECT department, name, salary
FROM ranked
WHERE salary_rank = 2
ORDER BY department;

结果：HR – Grace（4800）、IT – Eve（6500）、Sales – Alice（5000）。

在 Sales 部门，Bob 和 Carol 并列最高，所以第二高是 Alice 的 5000。只有一种薪水的部门不会出现在结果中。

如何选择解法

面试时，先给出 DENSE_RANK 解法，解释为什么不用 ROW_NUMBER 或 RANK，并说明如何返回 NULL。

练习

• 员工薪水排名
• 各部门薪水最高的员工
• 重置成本最高的电影

要点：

• 第二大的值是 MAX(x) WHERE x < (SELECT MAX(x) ...)；它能处理并列，不存在时返回 NULL。
• 使用 LIMIT 1 OFFSET N-1 时一定要加 DISTINCT，否则并列会让答案错位。
• 标量子查询能把“零行”变成 NULL。
• DENSE_RANK() 适用于任意 N，能返回对应的员工，配合 PARTITION BY 还能按组计算。
• 当值有重复时，ROW_NUMBER 和 RANK 会给出错误答案。

常见问题

如何在 SQL 中找出第二高的薪水？

最短且可移植的查询是 SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);。它返回低于最高薪水的最大薪水，不受并列影响，并且在所有人薪水相同时返回 NULL。

如何在 SQL 中找出第 N 高的薪水？

在 CTE 中使用 DENSE_RANK() OVER (ORDER BY salary DESC) 并筛选名次 N，或者使用 SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1。两种方法都能正确处理重复的薪水。

为什么不用 ROW_NUMBER 或 RANK？

ROW_NUMBER 会给相同的薪水不同的编号，所以最高薪水并列时，同一个值会排到第二位。RANK 在并列之后会跳号：有两个最高薪水时，根本不存在第 2 名。DENSE_RANK 对不同的值连续编号。

如果没有第 N 高的薪水，如何返回 NULL？

把查询放进标量子查询：SELECT (SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 9) AS result;。即使内层查询什么也没找到，外层查询仍会返回一行 NULL。