题解 | 获取员工其当前的薪水比其manager当前薪水还高

# 先找到每个员工当前薪水
with t1 as (
    select a.emp_no, a.dept_no, b.salary
    from dept_emp as a
    left join salaries as b
    on a.emp_no = b.emp_no
    and a.to_date = '9999-01-01'
    and b.to_date = '9999-01-01'
),
# 查找处每一个员工的经理
t2 as (
    select t1.*, c.emp_no as manager_no
    from t1
    left join dept_manager as c
    on t1.dept_no = c.dept_no
    and c.to_date = '9999-01-01'
),
# 找出每一个员工所对应的经理的薪资
t3 as (
    select t2.emp_no, t2.manager_no, t2.salary as emp_salary, d.salary as manager_salary
    from t2
    left join salaries as d
    on t2.manager_no = d.emp_no
)
# 找到比经理薪水还高的人的信息
select t3.*
from t3
where emp_salary > manager_salary;