题解 | #牛客每个人最近的登录日期(六)#

select mins.emp_no, (maxs.max_s-mins.min_s) growth
from 
    (
        select e.emp_no emp_no,min(salary) min_s
        from salaries s left join employees e on e.emp_no=s.emp_no
        where e.hire_date=s.from_date
        group by emp_no
        ) mins left join 
    (
        select emp_no,max(salary) max_s
        from salaries
        where to_date='9999-01-01'
        group by emp_no
    ) maxs on mins.emp_no=maxs.emp_no
where mins.emp_no in 
        ( select emp_no from salaries where to_date='9999-01-01')
group by mins.emp_no
order by growth asc