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

WITH dept_emp_salary AS (
    SELECT
        d.emp_no,
        d.dept_no,
        d.from_date AS dept_from_date,
        d.to_date   AS dept_to_date,
        s.salary,
        s.from_date AS salary_from_date,
        s.to_date   AS salary_to_date
    FROM dept_emp d
    LEFT JOIN salaries s
    USING (emp_no)
)
SELECT
    e.emp_no,
    dm.emp_no AS manager_no,
    e.salary AS emp_salary,
    m.salary AS manager_salary
FROM dept_emp_salary e
LEFT JOIN dept_manager dm
  ON e.dept_no = dm.dept_no
 AND dm.to_date = '9999-01-01'
LEFT JOIN dept_emp_salary m
  ON dm.emp_no = m.emp_no
 AND m.salary_to_date = '9999-01-01'
WHERE e.dept_to_date = '9999-01-01'
  AND e.salary_to_date = '9999-01-01'
  AND e.salary > m.salary;