题解 | #获取当前薪水第二多的薪水salary#

SELECT 
    e.emp_no,
    sub.salary AS second_highest_salary,
    e.last_name,
    e.first_name
FROM 
    employees e
JOIN 
    (SELECT s1.emp_no, s1.salary
     FROM salaries s1
     WHERE s1.salary = (
         SELECT MAX(s2.salary)
         FROM salaries s2
         WHERE s2.salary < (SELECT MAX(salary) FROM salaries)
     )
    ) AS sub ON e.emp_no = sub.emp_no;

解题思路是找到除最大薪水之外的最大薪水所在行，因此可以先用SELECT MAX(salary) FROM salaries找到最大薪水，然后通过where语句找到除它之外的最大薪水，作为子表与employees表进行连接。