题解 | 想不通的时候就动手在草稿纸上写写

select
    d1.emp_no emp_no,
    d2.emp_no manager_no,
    d1.salary emp_salary,
    d2.salary manager_salary
from
    (
        select
            de.emp_no,
            de.dept_no,
            sa.salary
        from
            dept_emp de
            inner join salaries sa on de.emp_no = sa.emp_no
            left join dept_manager dm on de.emp_no = dm.emp_no
            and de.dept_no = dm.dept_no
        where
            dm.emp_no is null
            and de.to_date = '9999-01-01'
    ) d1 #员工表
    join (
        select
            de.emp_no,
            de.dept_no,
            sa.salary
        from
            dept_emp de
            inner join salaries sa on de.emp_no = sa.emp_no
            left join dept_manager dm on de.emp_no = dm.emp_no
            and de.dept_no = dm.dept_no
        where
            dm.emp_no is not null
            and de.to_date = '9999-01-01'
    ) d2 #manager表
    on d1.dept_no = d2.dept_no
where
    d1.salary > d2.salary