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

SELECT emp.emp_no,dm.emp_no manager_no,emp.salary emp_salary,dm.salary manager_salary
FROM (
    SELECT a.emp_no,a.salary,b.dept_no
    FROM salaries a
    LEFT JOIN dept_emp b
    ON a.emp_no = b.emp_no
    WHERE a.emp_no NOT IN (
        SELECT DISTINCT emp_no
        FROM dept_manager
 )
) AS emp
LEFT JOIN (
    SELECT a.emp_no,b.salary,a.dept_no
    FROM dept_manager a
    LEFT JOIN salaries b
    ON a.emp_no = b.emp_no
) AS dm
ON emp.dept_no = dm.dept_no
WHERE emp.salary > dm.salary