题解 | #查找在职员工自入职以来的薪水涨幅情况#

SELECT a.emp_no, (b.salary - a.salary) AS growth
FROM(
    SELECT e.emp_no, e.first_name, e.last_name, e.hire_date,s.salary, s.from_date, s.to_date
    FROM employees e
    LEFT JOIN salaries s
    ON e.emp_no = s.emp_no
    WHERE e.hire_date = s.from_date
) As a  --- 刚开始的薪资
, (
    SELECT e.emp_no, e.first_name, e.last_name, e.hire_date,s.salary, s.from_date, s.to_date
    FROM employees e
    LEFT JOIN salaries s
    ON e.emp_no = s.emp_no
    WHERE s.to_date = '9999-01-01'
) AS b --- 目前或离职的薪资
WHERE a.emp_no = b.emp_no
ORDER BY growth ASC

先删选入职时的工资，用a表表示
再删选最近一次或者离职时的工资，用b表表示
然后根据b表和a表对应的emp_no，用salary相减，构造一个新的变量growth
然后按照growth进行升序排序