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

select emp_no, growth
from (
select emp_no, (salary-lag(salary,1)over(partition by emp_no order by to_date asc)) growth
from (
select s.emp_no, s.salary, s.to_date
from salaries s join employees e on s.emp_no=e.emp_no 
where (e.hire_date=s.from_date and s.emp_no in (select s.emp_no from salaries s join employees e on s.emp_no=e.emp_no where s.to_date='9999-01-01')) or to_date='9999-01-01') a #查询在职员工的入职的薪资和稳定的薪资即a表
) b #薪水涨幅表
where growth is not null
order by growth asc

先查询在职员工的入职的薪资和稳定的薪资，然后算出薪水涨幅growth