题解 | #获取当前部门中比管理者工资高的员工#

SELECT T1.emp_no, T2.manager_no, T1.emp_salary, T2.manager_salary
FROM ( 
    SELECT T1A.emp_no, T1A.dept_no, T1B.salary AS emp_salary
    FROM (
        SELECT emp_no, dept_no FROM dept_emp
        WHERE to_date = '9999-01-01'
    ) T1A
    INNER JOIN (
        SELECT emp_no, salary FROM salaries
        WHERE to_date = '9999-01-01'
    ) T1B
    USING(emp_no)
) T1
INNER JOIN (
    SELECT T2A.emp_no AS manager_no, T2A.dept_no, T2B.salary AS manager_salary
    FROM (
        SELECT emp_no, dept_no FROM dept_manager
        WHERE to_date = '9999-01-01'
    ) T2A
    INNER JOIN (
        SELECT emp_no, salary FROM salaries
        WHERE to_date = '9999-01-01'
    ) T2B
    USING(emp_no)
) T2 ON T1.dept_no = T2.dept_no 
        AND T1.emp_no != T2.manager_no
        AND T1.emp_salary > T2.manager_salary