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

【思路】
1.链接部门关系表和员工薪水表得到所有员工的薪水及信息
2.链接部门经理表和员工薪水表得到部门经理的薪水及信息
3.dept_no为链接，链接上述两个表
4.在第三步得到的表中以员工薪水大于经理薪水为筛选条件找出最终符合目标的数据
【代码如下】
select e_s.emp_no as emp_no, m_s.emp_no as manager_no, e_s.salary as emp_salary, m_s.manager_salary from
(select d.emp_no, d.dept_no, s.salary from dept_emp as d left join salaries as s on d.emp_no = s.emp_no) as e_s
left join
(select dm.dept_no, dm.emp_no, s.salary as manager_salary from dept_manager as dm left join salaries as s on dm.emp_no = s.emp_no) as m_s
on
e_s.dept_no = m_s.dept_no
where e_s.salary > m_s.manager_salary
【思路二】不运用链接直接用多重子查询
1.
【代码如下】
select de.emp_no,dm.emp_no as manager_no,
s1.salary as emp_salary,s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.salary>s2.salary