题解 | #题解#

-- 创建临时表 tem_1，选择除部门经理外其他员工的员工号、部门号和薪水
with tem_1 as ( 
    select a.emp_no, a.dept_no, sa.salary 
    from (
        select de.emp_no, de.dept_no
        from dept_emp de, dept_manager dm
        where de.dept_no = dm.dept_no and de.emp_no != dm.emp_no
    ) a, salaries sa 
    where a.emp_no = sa.emp_no 
),
-- 创建临时表 tem_2，选择部门经理的员工号、部门号和薪水
tem_2 as (
    select b.emp_no, b.dept_no, sa.salary 
    from (
        select de.emp_no, de.dept_no
        from dept_emp de, dept_manager dm
        where de.dept_no = dm.dept_no and de.emp_no = dm.emp_no
    ) b, salaries sa 
    where b.emp_no = sa.emp_no 
)
-- 选取 tem_1 和 tem_2 中符合薪水高于部门经理的员工号、部门经理号以及员工和部门经理的薪水
select t1.emp_no, t2.emp_no as manager_no, t1.salary as emp_salary, t2.salary as manager_salary 
from tem_1 t1, tem_2 t2 
where t1.dept_no = t2.dept_no and t1.salary > t2.salary