题解 | #获取薪水第二多的emp_no以及salary#

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

难点主要在于where和子查询的多层嵌套。

因为不允许用order by，所以先用where salary < (select max(salary) from salaries) 取出除开最大工资的其他员工，

(select max(salary) from salaries where salary < (select max(salary) from salaries)) ，取出第二大的，然后将值通过where赋给salary，这是定位到个体，最后才考虑连表查全信息。