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

with t1 as    
(
select emp_no,salary, to_date
from salaries
where emp_no in (select emp_no from salaries where to_date = '9999-01-01')), 
t2 as(
    select 
    emp_no,salary,
    lag(salary,1) over (partition by emp_no order by to_date) as last_time_salary 
    from t1
)
select emp_no,sum(salary-last_time_salary) as growth from t2 group by emp_no order by growth；

1、select emp_no from salaries where to_date = '9999-01-01' 查询出在职员工的emp_no，再通过 in 取出工资表中在职员工的所有工资记录

2、lag(salary,1) over (partition by emp_no order by to_date) 用lag()函数，对员工分组按照to_date日期排序，在每一行后取出上一次的工资

3、最后按照emp_no 分组，计算工资差的和即为薪资涨幅