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

/*先贴上评论区大佬的极简代码！
select e.emp_no, j.salary - s.salary as growth
from employees e
join salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date
join salaries j on e.emp_no = j.emp_no and j.to_date = '9999-01-01'
order by growth;
*/
-- 我的方法是在没有员工表employees的情况下使用的，算是把简单问题复杂化
 -- 找在职员工的所有薪资记录 salaries就不要用了
with act_emp as (
    select rank() over (partition by s.emp_no order by to_date asc ) first_salary_rk,
           rank() over (partition by s.emp_no order by to_date desc ) last_salary_rk,
        s.*
    from salaries s
    where s.emp_no in (
        select emp_no from salaries where to_date='9999-01-01'
    )
),-- 找在职员工最开始的工资
    act_emp_f as (
    select emp_no,salary
    from act_emp
    where act_emp.first_salary_rk=1
),-- 找在职员工最后的工资
    act_emp_l as (
    select emp_no,salary
    from act_emp
    where act_emp.last_salary_rk=1
)
select aef.emp_no,
       ael.salary-aef.salary growth
from act_emp_f aef
join act_emp_l ael on ael.emp_no=aef.emp_no
order by growth asc
;