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

【解题思路】

1.先从员工关系表中过滤掉manager的相关信息，并作为新表e

select * from dept_emp
where emp_no not in (select emp_no from dept_manager)

2.联结表e、salary, 查询员工的emp_no、dept_no、salary，并作为员工薪酬信息表a1

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'

3.重复1和2的方式，查询出manager的相关信息，作为新表a2

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'

4.联结表a1、a2，查询员工的emp_no、manager的manager_no、员工当前的薪水emp_salary、员工对应的manager当前的薪水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

5. 使用过滤函数having，获取员工其当前的薪水比其manager当前薪水还高的相关信息，得到最终结果

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;