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

将三个表拆分为两个表，然后过滤

源码：

SELECT a.emp_no,b.emp_no,a.salary,b.salary
FROM (
SELECT a.emp_no AS 'emp_no',a.dept_no AS 'dept_no',c.salary AS 'salary'
FROM dept_emp a
JOIN dept_manager b
ON a.emp_no <> b.emp_no AND a.dept_no = b.dept_no
JOIN salaries c
ON c.emp_no = a.emp_no
) a,(
SELECT a.emp_no AS 'emp_no',a.dept_no AS 'dept_no',c.salary AS 'salary'
FROM dept_emp a
JOIN dept_manager b
ON a.emp_no = b.emp_no AND a.dept_no = b.dept_no
JOIN salaries c
ON c.emp_no = a.emp_no
) b
WHERE a.salary > b.salary AND a.dept_no = b.dept_no