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

SELECT b3.emp_no, MAX(b3.end_salary)-MAX(b3.start_salary) AS growth
FROM (
    SELECT E.emp_no, b2.salary, b2.from_date, b2.to_date,
    CASE WHEN b2.from_date=b2.minfrom_date THEN b2.salary ELSE 0 END AS start_salary,
    CASE WHEN b2.to_date=b2.maxto_date THEN b2.salary ELSE 0 END AS end_salary
    FROM employees E
    # salaries表格----筛选出在职员工信息,以及最初&最近一次的薪水
    JOIN (SELECT *
        FROM (
            SELECT emp_no, salary, from_date, to_date,
            MIN(from_date) OVER (PARTITION BY emp_no) AS minfrom_date,
            MAX(to_date) OVER (PARTITION BY emp_no) AS maxto_date
            FROM salaries
            ) AS b1
        WHERE b1.maxto_date='9999-01-01'
        AND (b1.from_date=b1.minfrom_date OR b1.to_date=b1.maxto_date)
        ) AS b2
    ON E.emp_no=b2.emp_no
) AS b3
GROUP BY b3.emp_no
ORDER BY growth