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

select s.emp_no,t.emp_no as manager_no,s.salary as emp_salary,t.salary as manager_salary
from (select dept_no,emp.emp_no,salary from dept_emp as emp join salaries as sal on emp.emp_no=sal.emp_no and emp.to_date='9999-01-01' and sal.to_date='9999-01-01') as s
left join (select dept_no,mar.emp_no,salary from dept_manager as mar join salaries as sal on mar.emp_no=sal.emp_no and mar.to_date='9999-01-01' and sal.to_date='9999-01-01') as t
on s.dept_no=t.dept_no
where s.salary> t.salary;

先将员工表和薪水表做内连，相连条件是1、员工编号相等，2、两者都是‘9999-01-01’状态；得出全部在职员工的薪水表；然后用同样的方法将领导表和薪水表做内连；得出全部在职的领导的薪水表；两个表均筛选出dept_no,emp_no,salary；
然后全部在职员工薪水表和全部在职的领导薪水表做左连接，连接条件是：部门编号相同，连接后用where做条件筛选出员工表中的员工薪水大于领导表中员工薪水的人；