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

select e_emp_no, m_emp_no, e_salary, m_salary
from
	(select e_emp_no, salary as e_salary, dept_no
	from salaries as s, 
	(select e.emp_no as e_emp_no, e.dept_no as dept_no
	from dept_emp as e left outer join dept_manager as m
	on e.dept_no = m.dept_no
	where e.emp_no <> m.emp_no) as edm
	where s.emp_no = edm.e_emp_no
	and to_date = "9999-01-01") as es
left outer join
	(select m.emp_no as m_emp_no, salary as m_salary, dept_no
	from salaries as s, dept_manager as m
	where s.emp_no = m.emp_no
	and s.to_date = "9999-01-01") as ms
on es.dept_no = ms.dept_no
where es.e_salary > ms.m_salary;

这一题感觉不用这么麻烦，可是大概据我目前所知的知识，也只能使用这个笨法子了。

本题的大致思路为：

1. 联立员工表和领导表，建立非领导的员工信息的临时表1；
2. 联立表1 和薪水表，建立非领导的员工信息及当前薪水的临时表2；
3. 联立领导表与薪水表，建立领导信息和薪水的临时表3；
4. 联立表3和表4，过滤相关信息得到需求信息。

代码编写过程如下：

# 第一步
select e.emp_no as e_emp_no, e.dept_no as dept_no, m.emp_no as m_emp_no
from dept_emp as e left outer join dept_manager as m
on e.dept_no = m.dept_no
where e.emp_no <> m.emp_no;

# 第二步
select e_emp_no, salary as e_salary, dept_no
from salaries as s, 
(select e.emp_no as e_emp_no, e.dept_no as dept_no
from dept_emp as e left outer join dept_manager as m
on e.dept_no = m.dept_no
where e.emp_no <> m.emp_no) as edm
where s.emp_no = edm.e_emp_no
and to_date = "9999-01-01";

# 第三步
select m.emp_no as m_emp_no, salary as m_salary, dept_no
from salaries as s, dept_manager as m
where s.emp_no = m.emp_no
and s.to_date = "9999-01-01"; 

# 第四步
select e_emp_no, m_emp_no, e_salary, m_salary
from
	(select e_emp_no, salary as e_salary, dept_no
	from salaries as s, 
	(select e.emp_no as e_emp_no, e.dept_no as dept_no
	from dept_emp as e left outer join dept_manager as m
	on e.dept_no = m.dept_no
	where e.emp_no <> m.emp_no) as edm
	where s.emp_no = edm.e_emp_no
	and to_date = "9999-01-01") as es
left outer join
	(select m.emp_no as m_emp_no, salary as m_salary, dept_no
	from salaries as s, dept_manager as m
	where s.emp_no = m.emp_no
	and s.to_date = "9999-01-01") as ms
on es.dept_no = ms.dept_no
where es.e_salary > ms.m_salary;