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

/*入职时候的工资表
SELECT e.emp_no,salary from salaries s
left join employees e 
on s.emp_no=e.emp_no
WHERE s.from_date = e.hire_date;
*/

/*离职或最后一次工资变动时的工资表
SELECT e.emp_no,salary from salaries s
left join employees e
on s.emp_no=e.emp_no
where s.to_date='9999-01-01';
*/

SELECT a.emp_no,(b.salary-a.salary) as growth
from (
    SELECT e.emp_no,salary from salaries s
    left join employees e 
    on s.emp_no=e.emp_no
    WHERE s.from_date = e.hire_date
) as a
inner join (      #a表中所有员工工资都有，b表中只有在职员工的工资，所以用内连接，用左外连接离职员工工资涨幅会显示为null
    SELECT e.emp_no,salary from salaries s
    left join employees e
    on s.emp_no=e.emp_no
    where s.to_date='9999-01-01'
) as b
on a.emp_no=b.emp_no
order by growth asc;