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

with t1 as 
(select dept_no,max(salary) max_salary   #每个部门领导的薪资
from dept_manager join salaries using(emp_no)
group by dept_no),
t2 as
(select dept_emp.emp_no,dept_emp.dept_no,salary,max_salary  #员工工资
from dept_emp join salaries using(emp_no) join t1 on dept_emp.dept_no=t1.dept_no
where dept_emp.emp_no not in
(select emp_no
from dept_manager
) and salary>max_salary)

select t2.emp_no emp_no,dept_manager.emp_no manager_no,salary emp_salary,max_salary manager_salary
from t2 join dept_manager using(dept_no)