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

SELECT cur.emp_no, (cur.salary - pre.salary) AS growth
FROM (
        SELECT e.emp_no, s.salary
        FROM employees AS e INNER JOIN salaries AS s
        ON e.emp_no = s.emp_no
        AND e.hire_date = s.from_date
    ) AS pre  /* 入职时工资表 */
    INNER JOIN (
        SELECT e.emp_no, s.salary
        FROM employees AS e INNER JOIN salaries AS s
        ON e.emp_no = s.emp_no
        WHERE s.to_date = '9999-01-01' /* 当前仍在职 */
    ) AS cur /* 当前工资表 */
ON pre.emp_no = cur.emp_no
ORDER BY growth ASC