题解 | SQL217 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

WITH t1 AS (
	-- todo1：先过滤出当前的工资数据
    SELECT
    	*
    FROM salaries
    WHERE to_date = '9999-01-01'
),
t2 AS (
	-- todo2：找出最高工资
    SELECT
    	MAX(salary) AS max_salary
    FROM salaries
),
t3 AS (
	-- todo3：加字段
    SELECT
    	t1.emp_no,
    	t1.salary
    FROM t1
    JOIN t2
    WHERE t1.salary != t2.max_salary
),
t4 AS (
	-- todo4：第二次找最高工资
    SELECT
    	MAX(salary) AS max_salary
    FROM t3
),
t5 AS (
	-- todo5：找到第二高工资的数据
    SELECT
        t3.emp_no,
    	t3.salary
    FROM t3
    JOIN t4
    WHERE t3.salary = t4.max_salary
),
t6 AS (
	-- todo6:关联维度表
    SELECT
    	t5.emp_no,
    	t5.salary,
    	e.last_name,
    	e.first_name
    FROM t5
    LEFT JOIN employees e
    ON t5.emp_no = e.emp_no
)
SELECT * FROM t6; 

# 这道题的关键在于不能使用 order by 关键字！