方法一: 通过MAX配合<运算得到;用MAX函数,先查出最大salary,再利用<得到不含最大salary的子表,在子表上再求最大值
SELECT
e.emp_no,
s.salary,
e.last_name,
e.first_name
FROM
employees e
INNER 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
WHERE
salary < ( SELECT MAX( salary ) -- 第一步: 查出原表最高工资
FROM salaries
WHERE to_date = '9999-01-01' )
AND to_date = '9999-01-01'
)
**方法二:**通过自连接和<=号配合COUNT函数,可以推广到求薪水排名第N高的情况
SELECT
s.emp_no,
s.salary,
e.last_name,
e.first_name
FROM
salaries s
JOIN employees e ON s.emp_no = e.emp_no
WHERE
s.salary = (
SELECT
s1.salary
FROM
salaries s1
JOIN salaries s2 -- 自连接查询
ON s1.salary <= s2.salary
GROUP BY
s1.salary -- 当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2
HAVING
count( DISTINCT s2.salary ) = 2 -- (去重之后的数量就是对应的名次)
AND s1.to_date = '9999-01-01'
AND s2.to_date = '9999-01-01'
)
AND s.to_date = '9999-01-01'