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

select emp_no, salary
from salaries
where to_date = '9999-01-01';

select employees.emp_no, salary
from salaries, employees
where salaries.emp_no = employees.emp_no
and hire_date = from_date;

select now_salary.emp_no as emp_no, (now_salary.salary-first_salary.salary) as growth
from
(select emp_no, salary from salaries where to_date = '9999-01-01') as now_salary,
(select employees.emp_no as emp_no, salary from salaries, employees where salaries.emp_no = employees.emp_no and hire_date = from_date) as first_salary
where now_salary.emp_no = first_salary.emp_no;

select emp_no, growth
from
(select now_salary.emp_no as emp_no, (now_salary.salary-first_salary.salary) as growth
from
(select emp_no, salary from salaries where to_date = '9999-01-01') as now_salary,
(select employees.emp_no as emp_no, salary from salaries, employees where salaries.emp_no = employees.emp_no and hire_date = from_date) as first_salary
where now_salary.emp_no = first_salary.emp_no) as eg
order by growth;

一直以来，我都在说这一个暂时想不好怎么来命名的方法，就是从最简单之处开始，一步步封装，最后就可以实现我们的需求。

这个方法的缺点，可能在于效率不高，或者说占内存较多。

但是，一旦掌握了这个方法，在我看来，这些需求真的没有什么困难的，不同之处只是在于花费的时间。

这道题也是如此。 分四步走：

1. 根据薪水表查找当前在职员工的现阶段薪水与编号；
2. 根据两个表联结，查找入职时的员工薪水和员工编号；
3. 将2联结到1上，获得员工编号以及工资涨幅情况；
4. 重新选择并排序。