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

1.分析
1.可以通过MAX配合<运算得到
2.可以通过自连接和<=号配合COUNT函数找出排名高的薪水
补充：第二种方法可以推广到求薪水排名第N高的情况

2.代码

方法1：用MAX函数，先查出最大salary，再利用<得到不含最大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 to_date='9999-01-01'
AND salary=(SELECT max(salary)
            FROM salaries
            WHERE to_date='9999-01-01'
            AND salary<(SELECT MAX(salary)
            FROM salaries
            WHERE to_date='9999-01-01')
            );


方法2：通过自连接和<=号配合COUNT函数找出排名高的薪水
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 to_date='9999-01-01'
AND salary=(SELECT s1.salary
            FROM salaries AS s1
            INNER JOIN 
            salaries AS s2
            ON s1.to_date='9999-01-01'
            AND s2.to_date='9999-01-01'
            WHERE s1.salary <= s2.salary
            GROUP BY s1.salary
            HAVING COUNT(DISTINCT s2.salary)=2
            );