题解 | 利用with as拆解题目

---取每个部门经理的薪水以及部门号，员工编号
with masa as(
    select ma.dept_no,ma.emp_no,sa.salary as manager_salary
    from (select * from dept_manager where to_date='9999-01-01') ma left join (select * from salaries where to_date='9999-01-01') sa
    on ma.emp_no=sa.emp_no
),
---取每个员工的薪水以及部门号，员工编号
     emsa as(
    select em.dept_no,em.emp_no,sa.salary as emp_salary
    from  (select * from dept_emp where to_date='9999-01-01') em
left join (select * from salaries where to_date='9999-01-01') sa
    on em.emp_no=sa.emp_no
     )
---通过部门号来匹配每个员工以及对应经理，随后列出员工编号，经理编号，以及各自薪水
select emsa.emp_no,masa.emp_no as manager_no,emsa.emp_salary,masa.manager_salary
from masa left join emsa on masa.dept_no=emsa.dept_no
where emsa.emp_salary>masa.manager_salary