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

select
emp_no,
growth
from(
    select
emp_no,
salary - lag(salary,1,null) over(partition by emp_no)  growth
from
    (
        select
            emp_no,
            salary,
            from_date,
            to_date,
            row_number() over (
                partition by
                    emp_no
                order by
                    from_date
            ) rk,
            max(to_date) over (
                partition by
                    emp_no
            ) isin,
            count(emp_no)over (
                partition by
                    emp_no
            ) n
        from
            salaries
    ) t
    where isin = '9999-01-01' and (rk = 1 or rk = n) 

) a where growth is not null
order by growth

窗口函数