题解 | #SQL21 查找在职员工自入职以来的薪水涨幅情况 两步出结果#

方法：分别查出在职员工的最新薪资、入职薪资，做差即得

步骤：

1、查出在职员工的最新工资

select e.emp_no, salary latest_salary
from employees e
         left join salaries s on e.emp_no = s.emp_no
where to_date = '9999-01-01';

2、员工入职时的初始薪资

select e.emp_no, salary init_salary
from employees e
         left join salaries s on e.emp_no = s.emp_no
where hire_date = from_date;

最终SQL：

select t1.emp_no emp_no, (latest_salary - init_salary) growth
from (
         select e.emp_no,
                salary latest_salary
         from employees e
                  left join salaries s on e.emp_no = s.emp_no
         where to_date = '9999-01-01'
     ) t1
         left join
     (
         select e.emp_no, salary init_salary
         from employees e
                  left join salaries s on e.emp_no = s.emp_no
         where hire_date = from_date
     ) t2 on t1.emp_no = t2.emp_no
order by growth;