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

select 
t1.emp_no
,max(t2.salary-t1.salary) growth
from
(
    select e.emp_no,salary 
    from employees e join salaries s on e.emp_no=s.emp_no
    where from_date=hire_date
) t1 join
(
    select e.emp_no,salary 
    from employees e join salaries s on e.emp_no=s.emp_no
    where to_date='9999-01-01'
) t2 on t1.emp_no=t2.emp_no
group by t1.emp_no
order by growth

思路：对入职和离职的薪水分别建一个表连接，然后按条件查询