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

SELECT e.emp_no, s.salary, e.last_name, e.first_name
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no = s.emp_no
WHERE s.salary = (SELECT MAX(salary)       --2. Find 2nd highest salary
                  FROM salaries
                  WHERE salary < (
                  SELECT MAX(salary)            --1.Find highest salary
                  FROM salaries
                  WHERE to_date = '9999-01-01')
                  AND to_date = '9999-01-01'
                 )
AND s.to_date = '9999-01-01';              -- is for current employees

解法2：

WITH rank_table AS 
(SELECT *,
        RANK() OVER (ORDER BY salary DESC) AS rank
 FROM salaries
 WHERE to_date = '9999-01-01')

SELECT r.emp_no, r.salary, e.first_name, e.last_name
FROM rank_table AS r 
LEFT JOIN employees AS e
 ON r.emp_no = e.emp_no
WHERE rank = 2;