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

-- 首先需要把在职员工及当前薪水找出来
WITH 
employeed AS(
    SELECT emp_no,salary
    FROM salaries
    WHERE to_date = '9999-01-01'
    GROUP BY emp_no,salary
),
-- 第二步把员工的初始薪水找出来
original_salary AS(
    SELECT emp_no,salary,DENSE_RANK() OVER(partition by emp_no order by to_date) ranking
    FROM salaries
)
-- 再连接一下这两个表
SELECT t1.emp_no,t1.salary - t2.salary AS growth
FROM employeed t1
JOIN original_salary t2
ON t1.emp_no = t2.emp_no
WHERE t2.ranking = 1
ORDER BY growth