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

SELECT
    f.emp_no,
    e.manager_no,
    f.salary,
    e.salary 
    from (select
          -- 拉一张全员的薪资清单
            b.emp_no,
            b.dept_no,
            d.salary 
            from dept_emp b
                left join salaries d 
                on b.emp_no = d.emp_no and d.to_date='9999-01-01'
    ) as f
    left join (
            -- 拉一张管理员的薪资清单
        SELECT 
            b.emp_no as manager_no,
            b.dept_no,
            d.salary 
            from dept_emp b
              #连表条件为部门编号、员工编号都对应
              inner JOIN dept_manager c on c.dept_no=b.dept_no 
                                        and c.emp_no=b.emp_no 
                                        and b.to_date='9999-01-01'
              left join salaries d on b.emp_no = d.emp_no
               ) as e
               -- 连表条件是部门编号一致
    on f.dept_no=e.dept_no
    and e.dept_no like 'd%' 
    -- 筛选条件是非管理者的工资大于其管理者
    where f.salary>e.salary and f.emp_no !=e.manager_no;