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

-- 求出部门经理的薪水
WITH 
manager_salary AS(
    SELECT t1.emp_no,t2.salary,t1.dept_no
    FROM dept_manager t1
    LEFT JOIN salaries t2
    ON t1.emp_no = t2.emp_no
),
-- 找出薪水比部门经理高的员工id,薪水，再与部门经理薪水相连
no_employee AS(
    SELECT d1.emp_no,d3.emp_no manager_no,d2.salary emp_salary,d3.salary manager_salary
    FROM dept_emp d1
    JOIN salaries d2
    ON d1.emp_no = d2.emp_no
    JOIN  manager_salary d3
    ON d1.dept_no = d3.dept_no
    WHERE d2.salary > d3.salary
)
SELECT *
FROM no_employee