0176. Second Highest Salary¶
题目信息¶
- 题号:
第二高的薪水
- 难度:
Medium
- 主题:
Database、聚合、不同值排名、NULL
- 原题:
- 重点:
把第二高定义为“小于最高值的集合中的最大值”,并利用聚合保证空集合仍返回 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 上有合适索引,优化器可以更快定位最高值及其前一个不同值。物理计划可变,
“先排除最高等级,再取剩余最大值”的语义不变。