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

with a as(
    select emp_no from salaries where to_date = '9999-01-01' #找到符合要求的emp_no
),
b as(
    select s.emp_no ,salary, rank()over(partition by s.emp_no order by from_date ) r
    from salaries s, a
    where s.emp_no  = a.emp_no #排序
),
e as(
    select s.emp_no ,salary, rank()over(partition by s.emp_no order by from_date desc) r
    from salaries s, a
    where s.emp_no  = a.emp_no
),
c as(
    select emp_no, salary
    from b 
    where r = 1 #初始薪资
),
d as(
    select emp_no, salary
    from e
    where r = 1 #当前薪资
)
select c.emp_no, d.salary - c.salary growth
from c,d
where c.emp_no = d.emp_no
order by growth