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

步骤和答案

1. 查找在职员工的员工编号和当前薪水

   select emp_no, salary from salaries
   where to_date = '9999-01-01';

2. 查找所有员工的最初薪水

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

3. 查找在职员工的员工编号和薪水涨幅（当前薪水-最初薪水）

   select sCur.emp_no, (sCur.salary - sStart.salary) growth
   from (select emp_no, salary from salaries
    where to_date = '9999-01-01'
     ) sCur, /*From Step 1*/
    (select e.emp_no, salary
    from employees e, salaries s
    where e.emp_no = s.emp_no
    and e.hire_date = s.from_date
    ) sStart /*From Step 2*/
   where sCur.emp_no = sStart.emp_no /*inner join the tables from Step 1&2*/
   order by growth;

优化

看过评论之后，发现答案可以用两个inner join大幅简化，简化后的答案思路和上述答案基本一致

select a.emp_no, (b.salary - c.salary) as growth from employees as a
inner join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date /*Similar to step 2 above*/
inner join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01' /*Similar to step 1 above*/

order by growth;