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

# # 入职的薪水表
with a as
(SELECT e.emp_no,s.salary as first_salary
from employees e 
left join salaries s on e.emp_no = s.emp_no
where e.hire_date = s.from_date )

select num,(now_salary - first_salary) as growth 
from 
(select* from a
 LEFT JOIN
 (SELECT e.emp_no as num,s.salary as now_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.num
)c
where  num is not null 
and (now_salary - first_salary) is not NULL
order by growth