SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

1. 题目

• 题目来源：SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

• 题目描述
  查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成

• 表格
  
  

• 所需结果
  

2. 题解

注意，题目要求不能使用ORDER BY。

思路：从过滤掉最大值后的结果里面再次求最大值

子查询

# 正解
SELECT e.emp_no, s.salary, e.last_name, e.first_name
FROM employees e
  JOIN salaries s
  ON e.emp_no = s.emp_no
WHERE s.salary = (SELECT MAX(salary)
                  FROM salaries
                  WHERE salary != (SELECT DISTINCT MAX(salary) FROM salaries))

窗口函数

使用最大窗口函数（而不是排序窗口函数，规避了使用order by）一气呵成。

SELECT t.emp_no, t.salary, t.last_name, t.first_name
FROM (SELECT e.emp_no, s.salary, e.last_name, e.first_name,
             MAX(salary) OVER() max_salary
      FROM employees e 
      JOIN salaries s 
      ON e.emp_no = s.emp_no
      WHERE s.to_date = '9999-01-01'
            AND salary != (SELECT MAX(salary) FROM salaries)
     ) t 
WHERE salary = max_salary

反思：多字段聚合函数

一开始错误的做法：

# 错误：应该采用 WHERE子查询，而不是FROM 子查询
SELECT t.emp_no, t.salary, t.last_name, t.first_name
FROM (
SELECT e.emp_no, MAX(s.salary) max_salary, e.last_name, e.first_name
FROM employees e 
JOIN salaries s
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999=01-01'
      AND s.salary != (SELECT MAX(salary) FROM salaries)
GROUP BY e.emp_no, e.last_name, e.first_name
) t
WHERE 。。

使用 FROM子查询，导致直接陷入死循环，没有意识到可以使用 WHERE子查询 来对字段进行限制，而不是依赖聚合函数本身。