法一：一表多用

select de.emp_no,
       dm.emp_no as manager_no,
       s1.salary as emp_salary,
       s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.to_date='9999-01-01'
and s2.to_date='9999-01-01'
and s1.salary>s2.salary

13ms 3452KB

法二：分别构建员工工资表和manager工资表，再用部门号联结

员工工资表

SELECT de.dept_no, de.emp_no, s.salary AS emp_salary
FROM dept_emp de, salaries s
WHERE de.emp_no=s.emp_no
AND s.to_date='9999-01-01'

manager工资表

SELECT dm.dept_no, dm.emp_no AS manager_no, s.salary AS manager_salary
FROM dept_manager dm, salaries s
WHERE dm.emp_no=s.emp_no
AND s.to_date='9999-01-01'

最终SQL

SELECT a.emp_no, b.manager_no, a.emp_salary, b.manager_salary
FROM
(SELECT de.dept_no, de.emp_no, s.salary AS emp_salary
FROM dept_emp de, salaries s
WHERE de.emp_no=s.emp_no
AND s.to_date='9999-01-01') a,
(SELECT dm.dept_no, dm.emp_no AS manager_no, s.salary AS manager_salary
FROM dept_manager  dm, salaries s
WHERE dm.emp_no=s.emp_no
AND s.to_date='9999-01-01') b
WHERE a.dept_no=b.dept_no
AND a.emp_salary>b.manager_salary;