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

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

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

/*在职员工:to_date='9999-01-01'
  入职以来的涨薪:先给同一个员工的薪资时期排序(按照from_date升序);
  将最早薪资和最近薪资并列,构造临时表,计算差值。
*/
with t1 as(
    select *,
    row_number() over(partition by emp_no order by from_date) as grouped_rank
from employees
right join salaries using(emp_no)
where emp_no in(select emp_no from salaries
                where to_date='9999-01-01')
),
t2 as(
    select emp_no,
group_concat((case when grouped_rank=1 then salary else null end)) as first_salary,
group_concat((case when to_date='9999-01-01' then salary else null  end)) as last_salary
from t1
group by emp_no
)

select emp_no,
cast(last_salary-first_salary as decimal) as growth
from t2
order by growth

全部评论

相关推荐

听说改名字就能收到offer哈:Radis写错了兄弟
点赞 评论 收藏
分享
评论
点赞
收藏
分享
牛客网
牛客企业服务