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

select
emp_no,
(max(salary) - min(salary)) growth
from (
    select
    s.emp_no,
    s.salary,
    s.to_date
    from salaries s
    where (s.emp_no, s.from_date) in (select e.emp_no, e.hire_date from employees e)
    or year(s.to_date) = 9999
) s1

group by emp_no
having (max(salary) - min(salary)) >= 0 and sum(year(to_date)) > 9999
order by growth asc