拆解问题:
先找到员工的工号和薪水,经理的工号和薪水,再使用dept联结起来两个表
SELECT salaries.emp_no,dept_manager.emp_no AS manager_no,salaries.salary AS emp_salary,s1.salary AS manager_salary FROM salaries JOIN dept_emp ON salaries.emp_no=dept_emp.emp_no JOIN dept_manager ON dept_emp.dept_no=dept_manager.dept_no JOIN ( SELECT dept_manager.emp_no,salary FROM salaries,dept_manager WHERE salaries.emp_no=dept_manager.emp_no AND salaries.to_date='9999-01-01' AND dept_manager.to_date='9999-01-01')AS s1 ON s1.emp_no=dept_manager.emp_no WHERE salaries.to_date='9999-01-01' AND dept_manager.to_date='9999-01-01' AND salaries.salary>s1.salary
还可以用另外一种写法,看起来更清楚些
SELECT emp_no,manager_no,emp_salary,manager_salary FROM ( SELECT de.emp_no,dept_no,salary AS emp_salary FROM dept_emp AS de INNER JOIN salaries AS s1 ON de.emp_no=s1.emp_no WHERE de.to_date='9999-01-01' AND s1.to_date='9999-01-01') AS a JOIN (SELECT dm.emp_no AS manager_no,dept_no,salary AS manager_salary FROM dept_manager AS dm INNER JOIN salaries AS s2 ON dm.emp_no=s2.emp_no WHERE dm.to_date='9999-01-01' AND s2.to_date='9999-01-01') AS b ON a.dept_no=b.dept_no WHERE emp_salary>manager_salary