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

#确定选用的目标列，员工表员工号，经理表员工号，员工工资，经理工资
SELECT 
    de.emp_no,
    dm.emp_no manger_no,
    s.salary emp_salary,
    ss.salary manager_salary
FROM dept_emp de
#连接经理表获取经理员工号
LEFT JOIN dept_manager dm
ON de.dept_no = dm.dept_no
#连接薪水表获取员工当前工资
LEFT JOIN salaries s
ON de.emp_no = s.emp_no AND de.to_date = s.to_date
#连接薪水表获取经理员工当前工资
LEFT JOIN salaries ss
ON dm.emp_no = ss.emp_no AND dm.to_date = ss.to_date
#过滤高薪员工
WHERE s.salary > ss.salary;