题解 | #获取员工其当前的薪水比其manager当前..#

with
    s1 AS (
        SELECT
            d.emp_no,
            d.dept_no,
            s.salary emp_salary
        FROM
            dept_emp d
            INNER JOIN salaries s ON d.emp_no = s.emp_no
        WHERE
            d.to_date = '9999-01-01'
    ),
    s2 AS (
        SELECT
            de.emp_no manager_no,
            de.dept_no dept_no_2,
            s.salary manager_salary
        FROM
            dept_manager de
            INNER JOIN salaries s ON de.emp_no = s.emp_no
        WHERE
            de.to_date = '9999-01-01'
    ),
    s3 AS (
        SELECT
            *
        FROM
            s1
            INNER JOIN s2 ON s1.dept_no = s2.dept_no_2
    )
SELECT
    emp_no,
    manager_no,
    emp_salary,
    manager_salary
FROM
    s3
WHERE
    s3.emp_salary > s3.manager_salary