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

# 部门经理的工资表
# with m_s,em_s as 
# (select *from dept_manager d
# inner join salaries s
# on d.emp_no = s.emp_no) m_s
# # 员工的薪水（包括经理的）总表
# (select*from dept_emp em
# inner join salaries s 
# on em.emp_no = s.emp_no)em_s
select ems.emp_no as empno
,ms.emp_no as manager_no
,ems.salary as emp_salary
,ms.salary as manager_salary 
from 
(
select d.dept_no,d.emp_no,s.salary
 from dept_manager d
join salaries s
on d.emp_no = s.emp_no
) ms
 join 
(select em.dept_no,em.emp_no,s.salary 
from dept_emp em
join salaries s 
on em.emp_no = s.emp_no
)ems
on ms.dept_no =ems.dept_no
where ems.salary > ms.salary 
and ms.dept_no = ems.dept_no