如何将单员工第三高薪资查询适配至所有员工（禁用dense_rank等函数）

需求说明 #

原SQL仅能计算emp_no=1单个员工的第三高薪资，现需扩展为对employee表中所有员工，分别计算其第三高薪资，且禁止使用dense_rank()/rank()等排名函数。

解决方案SQL #

SELECT 
    e.emp_no,
    MAX(e.salary) AS third_highest_salary
FROM 
    employee e
WHERE 
    e.salary < (
        -- 子查询：获取当前员工的第二高薪资（小于最高薪资的最大值）
        SELECT MAX(salary)
        FROM employee
        WHERE emp_no = e.emp_no
          AND salary NOT IN (
              -- 最内层：获取当前员工的最高薪资
              SELECT MAX(salary)
              FROM employee
              WHERE emp_no = e.emp_no
          )
    )
GROUP BY 
    e.emp_no

逻辑解释 #

1. 最内层子查询：针对每个员工，提取其薪资中的最大值（第一高薪资）。
2. 中间层子查询：在当前员工的薪资中，排除第一高薪资后，取剩余薪资的最大值，即第二高薪资。
3. 外层查询：在当前员工的薪资中，筛选出小于第二高薪资的记录，再取最大值，即为第三高薪资；最后按emp_no分组，得到每个员工的结果。

示例验证 #

针对提供的示例表：

执行上述SQL后，结果为：

完全符合需求中的预期结果。

特殊情况说明 #

若某员工的薪资种类不足3种（比如只有2种或1种），该员工对应的third_highest_salary会返回NULL，这是合理的——因为不存在第三高薪资。

内容的提问来源于stack exchange，提问作者gautam