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

M.emp_no AS emp_no,
N.emp_no AS manager_no,
M.salary AS emp_salary,
N.salary AS manager_salary
FROM
(
-- 员工薪水
SELECT 
T1.emp_no,
T1.salary,
T2.dept_no
FROM
salaries T1
LEFT JOIN
dept_emp T2
ON T1.emp_no = T2.emp_no
WHERE
T1.emp_no NOT IN (SELECT  emp_no FROM dept_manager )
)M
INNER JOIN
(
-- MANAGE薪水
SELECT 
T1.emp_no,
T1.salary,
T2.dept_no
FROM
salaries T1
LEFT JOIN
dept_emp T2
ON T1.emp_no = T2.emp_no
WHERE
T1.emp_no IN (SELECT  emp_no FROM dept_manager )
)N
ON
M.dept_no = N.dept_no
AND 
M.salary > N.salary