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

select emp_no,sum(g1) growth from (
    select emp_no,salary-lag(salary)over(partition by emp_no order by to_date) g1 from salaries
) as t1
group by emp_no
having emp_no in (select emp_no from salaries
                   where to_date='9999-01-01')
order by growth
;

分享一个使用偏移函数的方法：

偏移函数：

lag(字段名,偏移量[,默认值])over( [partition by 字段名] order by 字段名 asc|desc ) 向前偏移，向下走

lead(字段名,偏移量[,默认值])over( [partition by 字段名] order by 字段名 asc|desc ) 向后偏移，向上走

本题：

（1）使用偏移函数计算出每位员工的薪水涨幅，我理解的是先计算出每位员工，每次工资的涨幅

select emp_no,salary-lag(salary)over(partition by emp_no order by to_date) g1 from salaries

salary-lag(salary)over(partition by emp_no order by to_date) #使用偏移函数求每位员工每次的工资涨幅，偏移函数参数：

根据员工号分组，按照时间顺序排序。

（2）再将每次的涨幅求和，即员工从开始到当前的总涨幅：

select emp_no,sum(g1) growth from ( #每次涨幅求和即总涨幅

select emp_no,salary-lag(salary)over(partition by emp_no order by to_date) g1 from salaries

) as t1

group by emp_no

having emp_no in (select emp_no from salaries

where to_date='9999-01-01') #此处筛选仍然在职的员工（我之前一直没做对就是差了这一个条件。。。搞了好久555）

order by growth