题解 | SQL219 获取员工其当前的薪水比其manager当前薪水还高的相关信息

WITH t0 AS(
	-- 在职经理的编号
	SELECT
    	emp_no
    FROM salaries
    WHERE to_date = '9999-01-01'
    AND emp_no IN
    (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')
    GROUP BY emp_no
) ,
t1 AS(
    -- 在职员工的编号
	SELECT
    	emp_no
    FROM salaries
    WHERE to_date = '9999-01-01'
    AND emp_no NOT IN (SELECT emp_no FROM t0)
    GROUP BY emp_no
),
t2 AS (
    -- 在职员工当前的薪资
	SELECT
    	a.emp_no,
    	a.salary AS emp_salary,
    	b.dept_no
    FROM salaries a
    LEFT JOIN dept_emp 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 AS manager_no,
    	a.salary AS manager_salary,
    	b.dept_no
    FROM salaries a
    LEFT JOIN dept_emp b
    ON a.emp_no = b.emp_no
    WHERE a.emp_no
    IN (SELECT emp_no FROM t0)
    AND a.to_date = '9999-01-01'
),
t4 AS (
    SELECT
    	t2.emp_no,
    	t3.manager_no,
    	t2.emp_salary,
    	t3.manager_salary
    FROM t2
    LEFT JOIN t3
    ON t2.dept_no = t3.dept_no
    WHERE t2.emp_salary > t3.manager_salary
)
SELECT * FROM t4;

# 先获取经理再获取职工的数据!