牛客网刷题之SQL-18.查找当前薪水排名第二多的员工编号emp_no

查找当前薪水排名第二多的员工编号emp_no

1. 题目

2. 解题

SELECT e.emp_no, MAX(s.salary) AS 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.to_date = '9999-01-01' 
AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')