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

不能使用order by

法一：先查询最多的薪水，再查询除此以外的最多的薪水

select s.emp_no,max(salary),last_name,first_name from employees e join salaries s on e.emp_no=s.emp_no 
where salary<>(select max(salary) from salaries)

法二：两张薪水表连接，辅助表的薪水比原表高，分组聚合，辅助表的去重记录数+1即各薪水值的排名

select s.emp_no,salary,last_name,first_name from employees e join salaries s on e.emp_no=s.emp_no 
where salary=(select s1.salary from salaries s1 join salaries s2 on s1.salary<s2.salary group by s1.salary having count(distinct s2.salary)=1)