题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
https://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
select b.emp_no,(b.salary-a.salary) as growth
from (select e.emp_no,s.salary
from employees e left join salaries s on e.emp_no=s.emp_no
WHERE e.hire_date=s.from_date)a -- 入职工资表
inner join
(select e.emp_no,s.salary
from employees e left join salaries s on e.emp_no=s.emp_no
where s.to_date='9999-01-01')b -- 现在工资表
on a.emp_no=b.emp_no
order by growth
题目要求：入职以来的薪水涨幅情况，那就可以用现在的薪资 - 入职的薪资
现在的薪资
select e.emp_no,s.salary
from employees e left join salaries s on e.emp_no=s.emp_no
where s.to_date='9999-01-01'
入职时的薪资
select e.emp_no,s.salary
from employees e left join salaries s on e.emp_no=s.emp_no
WHERE e.hire_date=s.from_date
连接两表，按涨幅情况排序。完整代码如下
select b.emp_no,(b.salary-a.salary) as growth
from (select e.emp_no,s.salary
from employees e left join salaries s on e.emp_no=s.emp_no
WHERE e.hire_date=s.from_date)a -- 入职工资表
inner join
(select e.emp_no,s.salary
from employees e left join salaries s on e.emp_no=s.emp_no
where s.to_date='9999-01-01')b -- 现在工资表
on a.emp_no=b.emp_no
order by growth
