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

select emp_no,sum(growth) growth
from (
select  emp_no,growth
from(
select employees.emp_no,
salary-lag(salary,1)over(partition by emp_no order by to_date) growth
from employees join salaries on employees.emp_no = salaries.emp_no
where employees.emp_no in 
(
    select emp_no
    from salaries
    group by emp_no
    having max(to_date) = '9999-01-01'

    
)
) a
where a.growth is not null
) b
group by emp_no 
order by growth