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

select t1.emp_no,(sa1.salary-sa2.salary) growth 
from 
    (select emp_no,max(to_date) to_date,min(from_date) from_date from salaries group by 1
    having to_date = '9999-01-01')t1
    left join 
    salaries sa1 on sa1.to_date=t1.to_date and sa1.emp_no=t1.emp_no 
    left join 
    salaries sa2 on sa2.from_date=t1.from_date and sa2.emp_no=t1.emp_no
order by 2 asc

思路就是先找每个员工的最大【在职】时间和最小在职时间，然后再通过时间和工号连接薪资，最后做差比较排序即可。

容易忽略的就是在职需要to_date='9999-01-01'，如果第一个表里没有取的话，后续再取需要用inner join否则会有空白值报错，具体代码如下

select t1.emp_no,(sa1.salary-sa2.salary) growth 
from 
    (select emp_no,max(to_date) to_date,min(from_date) from_date from salaries group by 1)t1
    inner join 
    salaries sa1 on sa1.to_date=t1.to_date and sa1.emp_no=t1.emp_no and sa1.to_date='9999-01-01'
    left join 
    salaries sa2 on sa2.from_date=t1.from_date and sa2.emp_no=t1.emp_no
order by 2 asc