牛客 SQL：查找在职员工自入职以来的薪水涨幅情况

题目：查找在职员工自入职以来的薪水涨幅情况

先分别查找入职工资与当前工资，然后二者作差。

代码：

SELECT cur.emp_no, (cur.salary - pre.salary) AS growth
FROM 
(
    SELECT e1.emp_no, s1.salary
    FROM employees AS e1
    INNER JOIN salaries AS s1
    ON e1.emp_no = s1.emp_no
    WHERE e1.hire_date = s1.from_date
) AS pre

INNER JOIN 

(
    SELECT s2.emp_no, s2.salary
    FROM salaries AS s2
    WHERE s2.to_date = '9999-01-01'
) AS cur

ON pre.emp_no = cur.emp_no
order by growth