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

with b as (
    select distinct emp_no
    from salaries
    where to_date = '9999-01-01'
),
a as (
    select b.emp_no,salary,rank() over(partition by b.emp_no order by from_date) as rk
    from b left join salaries
    on b.emp_no = salaries.emp_no
),
c as (
    select emp_no,max(rk) as maxrk,min(rk) as minrk
    from a
    group by emp_no
),
d as (
    select c.emp_no,a1.salary - a2.salary as growth
    from c left join a as a1
    on c.emp_no = a1.emp_no and c.maxrk = a1.rk
    left join a as a2
    on c.emp_no = a2.emp_no and c.minrk = a2.rk
)

select *
from d
order by growth

怎么还有工资先涨后降的操作啊