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

# 条件：当前员工，则所有表的 to_date='9999-01-01'
# 思路：多表连接，将员工，部门，部门领导，员工薪水和部门领导薪水放到一行数据中，取员工薪水>部门领导薪水的记录即可

with temp as (
	  select dep.emp_no 
			,dep.dept_no
			,dem.emp_no as manager_no
			,sal1.salary as emp_salary
			,sal2.salary as manager_salary
			,if (sal1.salary>sal2.salary,1,0) as is_big
	  from dept_emp as dep
	  left join dept_manager as dem
	  on dep.dept_no=dem.dept_no
	  left join salaries as sal1
	  on dep.emp_no=sal1.emp_no
	  left join salaries as sal2
	  on dem.emp_no=sal2.emp_no
	  where sal1.to_date='9999-01-01'
	  and sal2.to_date='9999-01-01'
	  and dep.to_date='9999-01-01'
	  and dem.to_date='9999-01-01'
)
select emp_no
      ,manager_no
      ,emp_salary
      ,manager_salary
from temp
where is_big=1