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

首先，容易错误的地方，以在职期间最大薪水减去最小薪水，忽略了中途被降薪的case。
其次，按解题思路应该是在职员工最小日期对应的薪水减去最大日期的薪水。
1、获取在职员工的所有信息

select
    *
  from
    salaries
  where
    emp_no in   (   
                    select
                          emp_no
                    from
                          salaries
                    where
                          to_date = '9999-01-01'
                 ) 

2、在1的基础上获取在职员工最小日期对应的薪水

select
  emp_no,
  salary as hire_salary      -----最小日期对应的薪水
from (
  select
    *
  from
    salaries
  where
    emp_no in   (   
                    select
                          emp_no
                    from
                          salaries
                    where
                          to_date = '9999-01-01'
                 ) ) as tb
where                          ----- 获取条件为最小日期
  from_date in (select             
                  min(from_date)
                from
                  salaries
                group by
                  emp_no)

3、在1的基础上获取最大日期对应的薪水

select
    emp_no,
    salary as leave_salary      ----- 最大日期对应的薪水
  from (
    select
      *
    from
      salaries
    where
      emp_no in   (   
                    select
                          emp_no
                    from
                          salaries
                    where
                          to_date = '9999-01-01'
                 ) ) as t 
where                            ----- 获取条件为最大日期
  from_date in (select 
                  max(from_date)
                from 
                  salaries 
                group by
                  emp_no) 

4、将2和3使用join连接，连接条件为emp_no，并由在职员工最小日期对应的薪水减去最大期日期对应的薪水，即获得最终结果

select
  t1.emp_no, 
  leave_salary - hire_salary as growth
from
(
select
  emp_no,
  salary as hire_salary
from (
  select
    *
  from
    salaries
  where
    emp_no in   (   
                    select
                          emp_no
                    from
                          salaries
                    where
                          to_date = '9999-01-01'
                 ) ) as tb
where 
  from_date in (select min(from_date) from salaries group by emp_no)
) t1
join (
  select
    emp_no,
    salary as leave_salary
  from (
    select
      *
    from
      salaries
    where
      emp_no in   (   
                    select
                          emp_no
                    from
                          salaries
                    where
                          to_date = '9999-01-01'
                 ) ) as t
where 
  from_date in (select max(from_date) from salaries group by emp_no) ) t2
on 
  t1.emp_no = t2.emp_no
order by 
  growth