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

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

https://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5

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'

全部评论

相关推荐

点赞 收藏 评论
分享
牛客网
牛客企业服务