题解 | #获取员工其当前的薪水比其manager高

#要让员工的工资和其对应的manager工资在同一行,先构造全员工工资表和经理工资表
with emp_s as #在职的全体员工工资表
(
select
e.emp_no as emp_no
,e.dept_no as dept_no
,s.salary as salary

from dept_emp e left join salaries s
on e.emp_no =s.emp_no
where e.to_date = '9999-01-01' and s.to_date='9999-01-01'
)
, m_s as #经理工资表
(
select
m.dept_no
,m.emp_no
,s.salary
from dept_manager m left join salaries s
on m.emp_no = s.emp_no

)
, e_m_s as #员工与其主管工资对应表
(
select
es.emp_no as emp_no
,es.dept_no as dept_no
,es.salary as emp_salary
,ems.emp_no as manager_no
,ems.salary as manager_salary
,es.salary-ems.salary as diff_salary
from  emp_s es left join  m_s ems
on es.dept_no= ems.dept_no
)

select
emp_no
,manager_no
,emp_salary
,manager_salary
from e_m_s
where diff_salary>0
order by emp_no