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

# select c.emp_no,salary2-salary1 as growth
# from
# (select a1.emp_no,b1.salary salary1
# from employees a1
# join salaries b1
# on a1.emp_no=b1.emp_no
# where hire_date = from_date)c
# join
# (
# select a2.emp_no,salary salary2
# from employees a2
# join salaries b2
# on a2.emp_no=b2.emp_no
# where to_date='9999-01-01'
# )d
# on c.emp_no=d.emp_no
# order by growth

# select e.emp_no, (s2.salary-s1.salary) growth
# from employees e
# join salaries s1 on e.emp_no=s1.emp_no and e.hire_date=s1.from_date
# join salaries s2 on e.emp_no=s2.emp_no and s2.to_date='9999-01-01'
# order by growth

SELECT
  s1.emp_no,
  ( max( s2.salary ) - min( s1.salary ) ) growth 
FROM
  salaries AS s1,
  salaries AS s2 
WHERE
  s1.emp_no = s2.emp_no 
  AND s2.to_date = '9999-01-01' 
GROUP BY
  s1.emp_no 
ORDER BY
  growth