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

创建两张表（员工工资表sem、经理工资表sdm），通过比较表的薪资salary（sem.salary > sdm.salary）得出结果

SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT de.emp_no, de.dept_no, s.salary FROM dept_emp AS de INNER JOIN salaries AS s
    ON de.emp_no = s.emp_no AND s.to_date = "9999-01-01") AS sem,
 (SELECT dm.dept_no, dm.emp_no, s.salary FROM dept_manager AS dm INNER JOIN salaries AS s
ON dm.emp_no = s.emp_no AND s.to_date = "9999-01-01") AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

# ----------------
# SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
# FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de 
# ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem, 
# (SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm 
# ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
# WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary