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

select * from
(

SELECT
    t6.emp_no, (salary_1 - salary_0) AS growth #此处select emp_no需交代表名，用来区分emp_no具体来自哪张表。
FROM

(
    -- 初始工资表
    SELECT
        emp_no, salary AS salary_0
    FROM
    (
        SELECT 
            *, 
            DENSE_RANK() OVER (PARTITION BY emp_no ORDER BY to_date) AS a_rank
        FROM 
            salaries
    ) t3
    WHERE 
        a_rank = 1 #求每个员工最早的工资记录
) t4

right JOIN

(
    -- 涨薪后工资表
    SELECT
        emp_no, salary AS salary_1
    FROM
    (
        SELECT 
            *, 
            DENSE_RANK() OVER (PARTITION BY emp_no ORDER BY to_date DESC) AS t_rank
        FROM 
            salaries
    ) t5
    WHERE 
        t_rank = 1 and to_date='9999-01-01' #每个在职员工最晚的工资记录
) t6

ON 
    t4.emp_no = t6.emp_no

)t7

order by growth
;