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

select 
	dept_emp.emp_no,
	s1.emp_no as manager_no,  
	salaries.salary,
	s1.salary as manager_salary
from 
	salaries
join dept_emp
	on dept_emp.emp_no = salaries.emp_no
join (
	select 
		dept_manager.dept_no,
		dept_emp.emp_no,
		salaries.salary
	from 
		dept_emp 
	left join 
		salaries on
		salaries.emp_no = dept_emp.emp_no
	right JOIN 
		dept_manager on
		dept_manager.dept_no  = dept_emp.dept_no
	where dept_emp.emp_no = dept_manager.emp_no
) as s1
on s1.dept_no = dept_emp.dept_no
where s1.salary < salaries.salary
;

子查询获取所有manager的薪资

select 
	dept_manager.dept_no,
	dept_emp.emp_no,
	salaries.salary
from 
	dept_emp 
left join 
	salaries on
	salaries.emp_no = dept_emp.emp_no
right JOIN 
	dept_manager on
	dept_manager.dept_no  = dept_emp.dept_no
where dept_emp.emp_no = dept_manager.emp_no;