题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#
获取员工其当前的薪水比其manager当前薪水还高的相关信息
http://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
select em.emp_no,ma.emp_no as manager_no,em.salary as emp_salary,ma.salary as manager_salary
from
(select de.emp_no,de.dept_no,s.salary from
dept_emp as de left join salaries as s on de.emp_no=s.emp_no
where s.to_date='9999-01-01' and de.to_date='9999-01-01') as em
join
(select dm.emp_no,dm.dept_no,s.salary from
dept_manager as dm left join salaries as s on dm.emp_no=s.emp_no
where s.to_date='9999-01-01' and dm.to_date='9999-01-01') as ma
on em.dept_no=ma.dept_no
and em.salary>ma.salary
由于即使员工表里有经理的工资但是最终与经理表的工资比较时也会过滤掉em.salary>ma.salary，所以不需要在员工表里过滤经理
但是，因为没有过滤掉经理的工资，所以员工表里既有员工又有经理，所以不能使用left join，否则会多出来经理的记录
