两种方法:
1.使用自连接和count函数计算排名,再筛选薪水排名第二的员工信息
代码块
SELECT e.emp_no,s4.salary,last_name,first_name FROM employees AS e,salaries AS s4 WHERE e.emp_no=s4.emp_no AND s4.salary = ( SELECT s1.salary FROM salaries AS s1,salaries AS s2 WHERE s1.salary<=s2.salary AND s1.to_date='9999-01-01' AND s2.to_date='9999-01-01' GROUP BY s1.salary HAVING COUNT(DISTINCT s2.emp_no)=2)
2.在评论区看到一个答案,使用两次MAX函数,也能得到结果,思路简洁,在sqlite环境中能够通过,但是在mysql环境中语法没问题,结果和答案不一样。
这里是先找出最高的薪水,在连接employees表和salaries表时剔除最高薪水的记录,再去找连接后的表中最高的薪水
代码块
SELECT employees.emp_no,MAX(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')