SELECT f.emp_no, e.manager_no, f.salary, e.salary from (select -- 拉一张全员的薪资清单 b.emp_no, b.dept_no, d.salary from dept_emp b left join salaries d on b.emp_no = d.emp_no and d.to_date='9999-01-01' ) as f left join ( -- 拉一张管理员的薪资清单 SELECT b.emp_no as manager_no, b.dept_no, d.salary from dept_emp b #连表条件为部门编号、员工编号都对应 inner JOIN dept_manager c on c.dept_no=b.dept_no and c.emp_no=b.emp_no and b.to_date='9999-01-01' left join salaries d on b.emp_no = d.emp_no ) as e -- 连表条件是部门编号一致 on f.dept_no=e.dept_no and e.dept_no like 'd%' -- 筛选条件是非管理者的工资大于其管理者 where f.salary>e.salary and f.emp_no !=e.manager_no;