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

WITH t1 AS(
    -- 在职员工的编号
	SELECT
    	emp_no
    FROM salaries
    WHERE to_date = '9999-01-01'
    GROUP BY emp_no
),
t2 AS (
	SELECT
    	a.emp_no,
    	a.salary AS max_salary
    FROM salaries a
    LEFT JOIN employees b
    ON a.emp_no = b.emp_no
    WHERE a.emp_no
    IN (SELECT emp_no FROM t1)
    AND a.to_date = '9999-01-01'
),
t3 AS (
	SELECT
    	a.emp_no,
    	a.salary
    FROM salaries a
    LEFT JOIN employees b
    ON a.emp_no = b.emp_no
    WHERE a.emp_no
    IN (SELECT emp_no FROM t1)
    AND a.from_date = b.hire_date
),
t4 AS (
	SELECT
    	t2.emp_no,
    	max_salary - salary AS growth
    FROM t2
    LEFT JOIN t3
    ON t2.emp_no = t3.emp_no
    ORDER BY growth ASC
)
SELECT * FROM t4;

# 记住两个时间点，一个是入职，一个是现在，薪资相减去！