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

select a.emp_no,b.emp_no,max_emp,max_man from ( select p.emp_no,p.dept_no from dept_emp p left join dept_manager de on p.emp_no=de.emp_no and p.dept_no=de.dept_no where de.emp_no is null) a join( select emp_no, max(salary) max_emp from salaries group by emp_no) s1 on a.emp_no=s1.emp_no join( select dept_no,de.emp_no,max(salary) max_man from dept_manager de join salaries s2 on de.emp_no=s2.emp_no group by dept_no,emp_no) b on a.dept_no=b.dept_no where max_emp> max_man