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

# select emp_no, (max(salary) - min(salary)) growth 
# from salaries
# where emp_no in (select if(to_date = '9999-01-01', emp_no, null) working from salaries)  # 获得在职员工id
# group by emp_no 
# order by growth asc 

# 获得涨幅
select t1.emp_no, (t2.end_salary - t1.start_salary) growth 
from 

(select e.emp_no emp_no, s.salary start_salary # 获得所有员工开始薪水
from employees as e, salaries as s 
where e.emp_no = s.emp_no and e.hire_date = from_date) as t1, 

(select emp_no, salary end_salary # 获得在职员工最新薪水
from salaries 
where to_date = '9999-01-01') as t2

where t1.emp_no = t2.emp_no 
order by growth asc 

注释掉的是默认只有涨薪没有降薪，用两个子查询获得开始和最新的薪水