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

/*-- 窗口函数，运行慢
with a as
(select e.emp_no
        ,salary
        ,from_date
        ,to_date
        ,(last_value(salary) over w) - (FIRST_VALUE(salary) over w) growth -- 测试能不能窗口函数四则/ok
        ,rank() over (partition by e.emp_no order by to_date desc) pos
from employees e
join salaries s
on e.emp_no = s.emp_no
window w as (partition by e.emp_no order by to_date asc)
)
SELECT emp_no
       ,growth
from a
where pos = 1 and emp_no IN (    -- 最近的工资减去入职工资
                             SELECT emp_no   -- 关键点，如果工资表中没有9999就是离职了
                             FROM salaries
                             WHERE to_date = '9999-01-01'
                             )
order by growth*/
-- 在职员工（工资中todate存在9999），涨薪（todate最后和最前一次=入职日期那次的差）

-- 大佬的答案
select e.emp_no, (s1.salary-s2.salary) as growth 
/*两份salaries表, 用作查询当前薪水和初始薪水*/
from salaries as s1, salaries as s2, employees as e
/*当前薪水*/
where s1.to_date = '9999-01-01'
/*初始薪水*/
and s2.from_date = e.hire_date
/*保证是同一个人*/
and s1.emp_no = s2.emp_no
/*按人分组*/
group by e.emp_no
/*按growth升序*/
order by growth;

-- 开始的错误答案
/*SELECT *
from (
      SELECT emp_no
             ,salary - lag(salary,1) over (partition by emp_no order by from_date) growth
      from salaries
      where emp_no in (
                     select emp_no 
                     from salaries
                     where to_date = '9999-01-01'
                       )
     ) a
where growth is not null*/
-- 输出为01-09没有08和06，
/*with a as
(select e.emp_no
        ,salary
        ,from_date
        ,to_date
        ,(last_value(salary) over w) - (FIRST_VALUE(salary) over w) growth -- 测试能不能窗口函数四则/ok
from employees e
join salaries s
on e.emp_no = s.emp_no
window w as (partition by e.emp_no order by to_date asc)  -- 窗口函数小技巧
)

SELECT emp_no
       ,max(growth)
from a 
group by emp_no
having max(growth) > 0
order by max(growth)*/