题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
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