题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#

select a.emp_no,b.emp_no as manager_no,
a.salary as emp_salary,b.salary as manager_salary
from(
select b.dept_no,a.emp_no,a.salary  ------------------------------------
from salaries a
left join dept_emp b on a.emp_no=b.emp_no
where a.emp_no not in (select emp_no from dept_manager)
group by b.dept_no
)a                              -- ------------1、查出非manager的相关信息
left join 
(
select b.dept_no,a.emp_no,a.salary        ------------------------------------
from salaries a
left join dept_emp b on a.emp_no=b.emp_no
where a.emp_no in (select emp_no from dept_manager)
group by b.dept_no                        -- ----------2、查出manager的相关信息
)b on a.dept_no=b.dept_no
where a.salary>b.salary                   -- ----------3、员工薪水要比manager高