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

-- 要考虑降薪的问题，用最后的薪水减去入职薪水就成，不能直接用最大减去最小，有可能它最后又降薪了
select
s1.emp_no as emp_no,s1.salary-s2.salary as growth
from
-- 最后的薪水
(select emp_no,salary from salaries where to_date='9999-01-01') as s1
inner join 
-- 入职的薪水
(select
e1.emp_no as emp_no,salary 
from salaries e1 
inner join employees e2 on e1.emp_no=e2.emp_no and e1.from_date=e2.hire_date) as s2
on s1.emp_no=s2.emp_no
order by growth;