题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

题目：请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录）
题目问的有点模糊，一开始很难明确具体要怎么输出。但经过代码测试之后，我们慢慢的理清了需求：将仍在公司就职的员工自入职之日起至今的薪水变动列出。
思路一：

1. 首先，我们要将已离职的员工排除（即to_date不等于'9999-01-01'）,得到在职员工的编号

        select emp_no
        from salaries
        group by emp_no
        having max(to_date) != "9999-01-01"

2. 然后每个员工可能存在多条薪资变化的记录，我们将每一条记录都查询下来（使用自连接实现）

select s1.emp_no, (s2.salary - s1.salary) as growth
from salaries s1, salaries s2
where s1.to_date = s2.from_date and s1.emp_no = s2.emp_no 

3. 最后将同一个员工的每一条薪资变化进行汇总，并按照growth进行排序

    select emp_no, sum(growth) as total_growth
    from(
    select s1.emp_no, (s2.salary - s1.salary) as growth
    from salaries s1, salaries s2
    where s1.to_date = s2.from_date and s1.emp_no = s2.emp_no 
        and s1.emp_no not in (
        # 排除已经离职的员工
        select emp_no
        from salaries
        group by emp_no
        having max(to_date) != "9999-01-01"
        )
    ) t
    group by emp_no
    order by total_growth 

思路二

使用开窗函数来实现

1. 首先，我们要将已离职的员工排除（即to_date不等于'9999-01-01'）,得到在职员工的编号

        select emp_no
        from salaries
        group by emp_no
        having max(to_date) != "9999-01-01"

2. 我们只需找到每位员工距今最近的一条薪资记录与距今最远的一条记录，并作差即可得出该员工的薪资变化

 select emp_no, (last_value(salary) over w) - (first_value(salary) over w) as growth
    from salaries
    where emp_no not in (
    #排除已经离职的员工
        select emp_no
        from salaries
        group by emp_no
        having max(to_date) != "9999-01-01"
        )
    window w as (partition by emp_no order by to_date rows between unbounded preceding and unbounded following)

3.最后，将结果进行整理并排序

        select emp_no , growth
    from
    (
    select emp_no, (last_value(salary) over w) - (first_value(salary) over w) as growth
    from salaries
    where emp_no not in (
#             排除已经离职的员工
        select emp_no
        from salaries
        group by emp_no
        having max(to_date) != "9999-01-01"
        )
    window w as (partition by emp_no order by to_date rows between unbounded preceding and unbounded following)
    ) t 
    group by emp_no
    order by growth

这里最后的group by语句的作用是去重。具体的读者可自行测试