0176. Second Highest Salary

题目信息

题号:
  1. 第二高的薪水

难度:

Medium

主题:

Database、聚合、不同值排名、NULL

原题:

LeetCode 0176

重点:

把第二高定义为“小于最高值的集合中的最大值”,并利用聚合保证空集合仍返回 NULL

题目重述

Employee(id, salary) 表保存员工及薪资。查询第二高的 不同薪资值,并将结果列命名 为 SecondHighestSalary。

相同薪资只占一个等级;若表中少于两个不同薪资,查询仍须返回一行,其值为 NULL。

自建示例

Employee:
id | salary
1  | 4200
2  | 6100
3  | 6100
4  | 5300

输出:
SecondHighestSalary
5300

不同薪资等级为 6100、5300、4200,重复的 6100 不会占据第二名。
Employee:
id | salary
1  | 4800
2  | 4800

输出:
SecondHighestSalary
NULL

SQL 实现

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (
    SELECT MAX(salary)
    FROM Employee
);

题解

排序取第二行为什么会错

最直观的搜索方式是按 salary DESC 排序,再取第二行。但题目排名的是不同薪资,不是 员工:示例排序后的前两行都是 6100,直接偏移一行会再次得到最高薪资。

加入 DISTINCT 后,可以得到有序的薪资等级,再用 OFFSET 1 取第二个;这已经解决 重复值,却还有输出形状问题。若不存在第二个等级,普通行查询返回零行,而题目要求固定 返回一行 NULL。可以再用标量子查询包裹,但查询结构开始围绕排序位置和空行做补丁。

把名次改写为集合边界

第二高值有一个不依赖行位置的等价定义:

第二高薪资 = 所有严格小于最高薪资的薪资中的最大值

内层 MAX(salary) 先确定最高薪资。外层 WHERE salary < highest 删除所有最高薪资 行,无论最高薪资重复多少次都会被完整排除;剩余集合中的 MAX 就是第二高的不同值。

这里无需显式 DISTINCT。聚合函数关心的是最大值,重复的候选薪资不会改变最大值; “严格小于”同时保证结果不可能再次等于最高值。

空集合为何正好得到 NULL

若表为空,内层 MAX 为 NULL,比较 salary < NULL 不会为真,外层没有候选; 若所有员工薪资相同,严格小于最高值的行同样为空。

与普通 SELECT salary FROM ... 不同,不带 GROUP BY 的聚合查询会为整个输入集合 产生一行。MAX 在空集合上的结果是 NULL,所以外层查询自然返回:

SecondHighestSalary
NULL

这同时满足“固定一行”和“不存在时为空值”两个合同,不需要 CASE、COALESCE, 也不能把空值替换为 0,因为 0 可能本身就是合法薪资。

具体走读

对 4200、6100、6100、5300,内层聚合得到 6100。外层筛选只保留 4200、5300, 再取最大值得到 5300。两个 6100 都在严格小于条件处被排除,重复行不会进入第二阶段。

对 4800、4800,内层得到 4800,外层没有任何值严格小于它;外层 MAX 在空集合 上返回单行 NULL,而不是错误地把第二个员工的 4800 当作第二高。

执行代价

逻辑上查询完成两次极值查找:先求全局最大值,再求其下方的最大值。没有相关子查询,内层 结果是单个标量,不会为外层每一行重新定义。无索引时数据库通常需要线性扫描;若 salary 上有合适索引,优化器可以更快定位最高值及其前一个不同值。物理计划可变, “先排除最高等级,再取剩余最大值”的语义不变。