解题思路
#对新的拼接组合表进行查询,获取结果........第六步
select a1.emp_no,a2.emp_no as manager_no,a1.salary as emp_salary,a2.salary as manager_salary
from
(
#查询员工的emp_no,salary,dept_no.......第二步
select e.emp_no,s.salary,e.dept_no
from(
#判断哪个是员工,并查询员工的所有信息.......第一步
select * from dept_emp
where emp_no not in (select emp_no from dept_manager)) as e
inner join salaries as s
on s.emp_no = e.emp_no
where s.to_date='9999-01-01'
) as a1
#链接新的员工信息表a1和领导信息表a2......第五步
inner join
(
#查询领导的emp_no,salary,dept_no......第四步
select e2.emp_no,s.salary,e2.dept_no
from(
#判断哪个是领导,并获取领导的所有信息......第三步
select * from dept_emp
where emp_no in (select emp_no from dept_manager)) as e2
inner join salaries as s
on s.emp_no = e2.emp_no
where s.to_date='9999-01-01'
) as a2
on a1.dept_no = a2.dept_no
having emp_salary > manager_salary
实现代码
select a1.emp_no,a2.emp_no as manager_no,a1.salary as emp_salary,a2.salary as manager_salary
from
(
select e.emp_no,s.salary,e.dept_no
from(
select * from dept_emp
where emp_no not in (select emp_no from dept_manager)) as e
inner join salaries as s
on s.emp_no = e.emp_no
where s.to_date='9999-01-01'
) as a1
inner join
(
select e2.emp_no,s.salary,e2.dept_no
from(
select * from dept_emp
where emp_no in (select emp_no from dept_manager)) as e2
inner join salaries as s
on s.emp_no = e2.emp_no
where s.to_date='9999-01-01'
) as a2
on a1.dept_no = a2.dept_no
having emp_salary > manager_salary