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

# 当前 WHERE to_date = '9999-01-01'
# 员工薪水比manager薪水高(条件1：同一部门，条件2：员工薪水更高)
SELECT
    emp_no,
    manager_no,
    emp_salary,
    manager_salary
FROM(
    SELECT # 先找到部门领导薪水表
        dm.dept_no,
        dm.emp_no AS manager_no,
        salary AS manager_salary
    FROM dept_emp de
    JOIN dept_manager dm
    ON de.dept_no = dm.dept_no AND de.emp_no = dm.emp_no
    JOIN salaries s
    ON dm.emp_no = s.emp_no
) AS tb1
JOIN( # 表连接，使用部门dept_no连接
    SELECT  # 再找部门员工薪水（非领导）表
        de.dept_no,
        de.emp_no,
        salary AS emp_salary
    FROM dept_emp de
    JOIN salaries s
    ON de.emp_no = s.emp_no
    WHERE de.emp_no NOT IN (SELECT
                                emp_no
                            FROM dept_manager)
) AS tb2
ON tb1.dept_no = tb2.dept_no
WHERE emp_salary > manager_salary # 条件员工薪水更高