SQL21 查找在职员工自入职以来的薪水涨幅情况

描述

有一个员工表employees简况如下:

有一个薪水表salaries简况如下:

 

请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为

（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录）

 

 

select b.emp_no,(b.salary-a.salary) as growth
from
(SELECT employees.emp_no,salaries.salary FROM employees
left join salaries on employees.hire_date = salaries.from_date
and employees.emp_no = salaries.emp_no) a
join
(SELECT employees.emp_no,salaries.salary FROM employees
left join salaries on employees.emp_no = salaries.emp_no
where salaries.to_date='9999-01-01') b
on a.emp_no = b.emp_no
order by growth