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

select emp_no,manager_no,emp_salary,manager_salary
from (
      select emp_no,dept_no,manager_no,emp_salary
      from (
           select e.emp_no,m.emp_no manager_no,salary*(if(e.emp_no!=m.emp_no,1,0)) emp_salary,e.dept_no
           from dept_emp e 
           inner join salaries s on e.emp_no=s.emp_no
           left join dept_manager m on e.dept_no=m.dept_no 
           where s.to_date='9999-01-01'
         ) a
         where emp_salary!=0
) b left join
     (
     select m.dept_no,salary manager_salary
     from dept_manager m left join salaries s on m.emp_no=s.emp_no
     where s.to_date='9999-01-01'
     )  c 
on b.dept_no=c.dept_no
where b.emp_salary>c.manager_salary