0176. Second Highest Salary =========================== 题目信息 -------- :题号: 0176. 第二高的薪水 :难度: Medium :主题: Database、聚合、不同值排名、NULL :原题: `LeetCode 0176 `_ :重点: 把第二高定义为“小于最高值的集合中的最大值”,并利用聚合保证空集合仍返回 NULL 题目重述 -------- ``Employee(id, salary)`` 表保存员工及薪资。查询第二高的 **不同薪资值**,并将结果列命名 为 ``SecondHighestSalary``。 相同薪资只占一个等级;若表中少于两个不同薪资,查询仍须返回一行,其值为 ``NULL``。 自建示例 -------- .. code-block:: text Employee: id | salary 1 | 4200 2 | 6100 3 | 6100 4 | 5300 输出: SecondHighestSalary 5300 不同薪资等级为 6100、5300、4200,重复的 6100 不会占据第二名。 .. code-block:: text Employee: id | salary 1 | 4800 2 | 4800 输出: SecondHighestSalary NULL SQL 实现 -------- .. code-block:: sql SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary < ( SELECT MAX(salary) FROM Employee ); 题解 ---- 排序取第二行为什么会错 ~~~~~~~~~~~~~~~~~~~~~~ 最直观的搜索方式是按 ``salary DESC`` 排序,再取第二行。但题目排名的是不同薪资,不是 员工:示例排序后的前两行都是 6100,直接偏移一行会再次得到最高薪资。 加入 ``DISTINCT`` 后,可以得到有序的薪资等级,再用 ``OFFSET 1`` 取第二个;这已经解决 重复值,却还有输出形状问题。若不存在第二个等级,普通行查询返回零行,而题目要求固定 返回一行 ``NULL``。可以再用标量子查询包裹,但查询结构开始围绕排序位置和空行做补丁。 把名次改写为集合边界 ~~~~~~~~~~~~~~~~~~~~ 第二高值有一个不依赖行位置的等价定义: .. code-block:: text 第二高薪资 = 所有严格小于最高薪资的薪资中的最大值 内层 ``MAX(salary)`` 先确定最高薪资。外层 ``WHERE salary < highest`` 删除所有最高薪资 行,无论最高薪资重复多少次都会被完整排除;剩余集合中的 ``MAX`` 就是第二高的不同值。 这里无需显式 ``DISTINCT``。聚合函数关心的是最大值,重复的候选薪资不会改变最大值; “严格小于”同时保证结果不可能再次等于最高值。 空集合为何正好得到 NULL ~~~~~~~~~~~~~~~~~~~~~~~~ 若表为空,内层 ``MAX`` 为 ``NULL``,比较 ``salary < NULL`` 不会为真,外层没有候选; 若所有员工薪资相同,严格小于最高值的行同样为空。 与普通 ``SELECT salary FROM ...`` 不同,不带 ``GROUP BY`` 的聚合查询会为整个输入集合 产生一行。``MAX`` 在空集合上的结果是 ``NULL``,所以外层查询自然返回: .. code-block:: text SecondHighestSalary NULL 这同时满足“固定一行”和“不存在时为空值”两个合同,不需要 ``CASE``、``COALESCE``, 也不能把空值替换为 0,因为 0 可能本身就是合法薪资。 具体走读 ~~~~~~~~ 对 ``4200、6100、6100、5300``,内层聚合得到 6100。外层筛选只保留 4200、5300, 再取最大值得到 5300。两个 6100 都在严格小于条件处被排除,重复行不会进入第二阶段。 对 ``4800、4800``,内层得到 4800,外层没有任何值严格小于它;外层 ``MAX`` 在空集合 上返回单行 ``NULL``,而不是错误地把第二个员工的 4800 当作第二高。 执行代价 ~~~~~~~~ 逻辑上查询完成两次极值查找:先求全局最大值,再求其下方的最大值。没有相关子查询,内层 结果是单个标量,不会为外层每一行重新定义。无索引时数据库通常需要线性扫描;若 ``salary`` 上有合适索引,优化器可以更快定位最高值及其前一个不同值。物理计划可变, “先排除最高等级,再取剩余最大值”的语义不变。