SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
描述
有一个员工表employees简况如下:
![](https://img2020.cnblogs.com/blog/1786811/202110/1786811-20211001175553061-2124075377.png)
有一个薪水表salaries简况如下:
请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不能使用order by完成,以上例子输出为:
select e.emp_no emp_no, s.salary salary, e.last_name last_name, e.first_name first_name from employees e inner join salaries s on e.emp_no = s.emp_no and s.to_date='9999-01-01' where s.salary = (select max( s2.salary) from salaries s2 where s2.salary < (select max(salary) from salaries where to_date='9999-01-01') and s2.to_date='9999-01-01');