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

方法一： 两表联查 然后再连接新水表

select t1.emp_no, t3.salary - t2.salary as growth
    from employees t1 join salaries t2 
    on t1.emp_no = t2.emp_no and t1.hire_date = t2.from_date
        join salaries t3 
        on t1.emp_no = t3.emp_no and t3.to_date = '9999-01-01'
order by growth;

方法二： 两表联查 条件 必须有一个表 to_date = '9999-01-01' 目的是为了获取到最高工资 以 emp_no 分组后 直接找出 salary 最小值 最高工资减最低工资即为涨幅情况

select t1.emp_no, (t2.salary - min(t1.salary)) growth
    from salaries as t1, salaries as t2
    where t1.emp_no = t2.emp_no and t2.to_date = '9999-01-01'
    group by t1.emp_no
    order by growth;