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

with t1 as
(select employees.emp_no,salary min_salary 
from employees join salaries using(emp_no)
where hire_date=from_date ),
t2 as
(select emp_no,salary max_salary,to_date
from salaries
where (emp_no,to_date) in
(select emp_no ,max(to_date) max_to_day
from salaries
group by emp_no
))

select t1.emp_no,(max_salary-min_salary) growth
from t1 join t2 using(emp_no)
where to_date='9999-01-01'
order by growth