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

/*在职员工：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