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

#建立表连接并求出每个员工的工资波动
select
table1.emp_no emp_no
,now_salary - hire_salary growth
from 
(
    #筛选出每个员工当前工资
    select
    emp_no
    ,salary now_salary
    from salaries 
    where emp_no in
    (
    #筛选出在职员工
    select
    emp_no
    from salaries
    where to_date = '9999-01-01'
    )
    and to_date = '9999-01-01'
)table1 inner join 
(
    #筛选出每个员工的入职薪资
    select
    t1.emp_no emp_no
    ,salary hire_salary
    from salaries t1 join employees t2 on t1.emp_no = t2.emp_no
    where t1.emp_no in
    (
    #筛选出在职员工
    select
    emp_no
    from salaries
    where to_date = '9999-01-01'
    )
    and from_date = hire_date
)table2 on table1.emp_no = table2.emp_no
order by 2