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

# select e.emp_no,max(salary)-min(salary) as growth
# from employees e
# left join salaries s
# on e.emp_no = s.emp_no
# where to_date='9999-01-01'
# group by e.emp_no
# order by growth

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
and 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