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

思路是去掉当前第一多的薪水后，在剩下的薪水里面找最多的。

一开始的做法（不太严谨）：

SELECT salaries.emp_no,salary,last_name,first_name FROM employees,salaries
WHERE employees.emp_no=salaries.emp_no AND to_date='9999-01-01' AND
salary = (SELECT MAX(salary) FROM salaries WHERE salary <(SELECT MAX(salary) FROM salaries))

完善一下上面的做法，在每次select里面都加上“当前”这个条件。

SELECT salaries.emp_no,salary,last_name,first_name FROM employees,salaries
WHERE employees.emp_no=salaries.emp_no AND 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'))