SELECT a.emp_no, a.manager_no, s1.salary emp_salary, s2.salary manager_salary FROM ( SELECT e.emp_no, m.emp_no manager_no FROM dept_emp e JOIN dept_manager m ON e.dept_no = m.dept_no WHERE e.to_date = "9999-01-01" AND m.to_date = "9999-01-01" ) a # 得到前两列 JOIN salaries s1 ON a.emp_no = s1.emp_no JOIN salaries s2 ON a.manager_no = s2.emp_no WHERE s1.salary > s2.salary
1. 合并员工no跟经理的no,得到前两列。为表a
2. join薪资表第一次,得到第三列
3. join薪资表第二次,得到第四列
4. 最后用where 筛选