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

select emp_no,sum(薪资涨幅) growth
from 
(
Select emp_no,salary-lag(salary,1)over(partition by emp_no order by from_date) 薪资涨幅 /*当前工资-之前工资=涨薪幅度*/
from salaries
where emp_no=any(select emp_no from salaries where to_date='9999-01-01') /*筛选在职员工*/
) ak
group by emp_no
order by growth

用偏移函数算出涨薪幅度，最后将涨薪幅度聚合，需要注意，条件筛选where emp_no=any(select emp_no from salaries where to_date='9999-01-01') 如果不加any会提示SQL_ERROR_INFO: 'Subquery returns more than 1 row'