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

思路：我们使用两个表，分别保存之前的工资和当前的工资

方法一：使用两次left join 和 一次 inner join

之前的工资的条件为：salaries.from_date = employees.hire_date

当前的工资的条件为：salaries.to_date = '9999-01-01'

获取这两个表之后，就可以通过 inner join 来进行计算了。

select sCurrent.emp_no, (sCurrent.salary - sStart.salary) as growth
from 
(select s.emp_no, s.salary from employees e left join salaries s on s.emp_no = e.emp_no where s.from_date = e.hire_date) as sStart,
(select s.emp_no, s.salary from employees e left join salaries s on s.emp_no = e.emp_no where s.to_date = '9999-01-01') as sCurrent
where sStart.emp_no = sCurrent.emp_no
order by growth

方法二：也使用两次内连接来实现取两个表的数据

第一次内连接求出当前工资，第二次内连接求出之前工资

select e.emp_no,(a.salary-b.salary) as growth
from
employees e
inner join
salaries a
on e.emp_no=a.emp_no and a.to_date='9999-01-01'
inner join
salaries b
on e.emp_no=b.emp_no and b.from_date=e.hire_date
order by growth asc;