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

with t2 as(
select 
    s1.emp_no,
    case when s1.to_date = '9999-01-01' then s1.salary else null end as new1,
    start_salary
from salaries s1
join (
select 
    s.emp_no,
    s.salary as start_salary,
    s1.salary as new_salary,
    s.from_date,
    s1.to_date,
    rank() over(partition by emp_no order by from_date) as ranking
from salaries s
left join salaries s1 on s.to_date = s1. from_date and s.emp_no = s1.emp_no   
) t1 on s1.emp_no = t1.emp_no and ranking = 1
)
select 
    emp_no,
    new1 - start_salary as growth
from t2
where new1 is not null 
order by growth