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

SELECT DISTINCT b1.emp_no, 
b2.emp_no AS manager_no, b1.salary AS emp_salary, 
b2.salary AS manager_salary
FROM
# 筛选所有当前员工的薪水&员工所在部门的manager
(   SELECT *
    FROM (
        SELECT DE.emp_no, DE.dept_no, DM.emp_no AS manager, S.salary,S.to_date,
        MAX(S.to_date) OVER (PARTITION BY DE.emp_no) AS Maxto_date
        FROM dept_emp DE
        JOIN salaries S ON DE.emp_no=S.emp_no
        JOIN dept_manager DM ON DE.dept_no=DM.dept_no) AS b0
    WHERE b0.to_date=b0.Maxto_date
    AND b0.Maxto_date='9999-01-01'
) AS b1

# 筛选所有部门manager的薪水
JOIN (SELECT DM.dept_no, DM.emp_no, S.salary
FROM dept_manager DM
JOIN salaries S ON DM.emp_no=S.emp_no) AS b2
ON b1.manager=b2.emp_no
WHERE b1.salary>b2.salary