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

select emp_no, (max(a)-max(b)) as growth
from(
select emp_no, 
if(to_date='9999-01-01',salary,null) a,
if(from_date=hire_date,salary,null) b
from
salaries left join employees using(emp_no)
where emp_no in (
    select emp_no from salaries 
    group by emp_no  
    having max(to_date) = '9999-01-01'
)
)as tb1
group by emp_no
order by growth