题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#
获取员工其当前的薪水比其manager当前薪水还高的相关信息
http://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
1、先查询出所有员工和对应部门manager的员工号和薪水 select de.emp_no, t.emp_no manager_no, s.salary emp_salary, t.salary manager_salary from dept_emp de left join salaries s on de.emp_no = s.emp_no left join (select dm.dept_no , dm.emp_no, salary from dept_manager dm left join salaries s on dm.emp_no = s.emp_no) t on t.dept_no = de.dept_no
2、判断员工薪水 高于 manager的薪水 select de.emp_no, t.emp_no manager_no, s.salary emp_salary, t.salary manager_salary from dept_emp de left join salaries s on de.emp_no = s.emp_no left join (select dm.dept_no , dm.emp_no, salary from dept_manager dm left join salaries s on dm.emp_no = s.emp_no) t on t.dept_no = de.dept_no where s.salary > t.salary
