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

with manager_salary as(
    select dm.emp_no,dept_no,dm.to_date,salary
    from dept_manager dm join salaries s using(emp_no)
    where dm.to_date = '9999-01-01'
),
emp_salary as(
    select de.emp_no,dept_no,de.to_date,salary
    from dept_emp de join salaries s using(emp_no)
    where de.to_date = '9999-01-01'
    and emp_no not in (select emp_no from dept_manager)
)
select es.emp_no,
       ms.emp_no manager_no,
       es.salary emp_salary,
       ms.salary manager_salary
from emp_salary es join manager_salary ms using(dept_no)
where es.salary > ms.salary

1. manager_salary得到在职经理的薪水
2. emp_salary得到在职员工的薪水，剔除了经理
3. emp_salary和manager_salary通过dept_no连接，where条件得到最终答案