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

1.将员工表和薪水表连接，新表为x

select e.emp_no, e.dept_no, s.salary 
from dept_emp as e join salaries as s on e.emp_no = s.emp_no

2.将经理表和薪水表连接，新表为y

select m.emp_no, m.dept_no, s.salary 
from dept_manager as m join salaries as s on m.emp_no = s.emp_no

经理只有一个，因此表x和表y按部门做左链接

3.员工比经理薪水高作为条件进行筛选，注意由于是左连接所以x表与y表按部门连接后y表将经理的信息全部补全到了真个员工表后面，若找工资相同的不可这么做

where emp_salary > manager_salary

总代码

select x.emp_no, y.emp_no manager_no, x.salary emp_salary, y.salary manager_salary
from(select e.emp_no, e.dept_no, s.salary 
from dept_emp as e join salaries as s on e.emp_no = s.emp_no) as x
left join (select m.emp_no, m.dept_no, s.salary 
from dept_manager as m join salaries as s on m.emp_no = s.emp_no) as y
on x.dept_no = y.dept_no
where emp_salary > manager_salary