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

最开始没有考虑降薪的情况 觉得不用第一张表哈哈哈

入职薪酬表

    select
      s.emp_no,
      s.salary
    from
      salaries as s,
      employees as e
    where
      s.emp_no = e.emp_no
      and s.from_date = e.hire_date

目前薪酬表

    select
      s.emp_no,
      s.salary
    from
      salaries as s,
      employees as e
    where
      s.emp_no = e.emp_no
      and s.to_date = '9999-01-01'

结果

select
  t1.emp_no,
  (t2.salary - t1.salary) as growth
from
  (
    select
      s.emp_no,
      s.salary
    from
      salaries as s,
      employees as e
    where
      s.emp_no = e.emp_no
      and s.from_date = e.hire_date
  ) as t1
  inner join (
    select
      s.emp_no,
      s.salary
    from
      salaries as s,
      employees as e
    where
      s.emp_no = e.emp_no
      and s.to_date = '9999-01-01'
  ) as t2 on t1.emp_no = t2.emp_no
order by
  growth