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

# 思路：构造一个员工薪水表、一个领导者薪水表，二者构成一个新表，然后匹配二者数据，比较
# 忽略了一个点：要求的是员工薪水比其Manager还高，所以需要做部门匹配

SELECT
emp_Temp.emp_no AS 'emp_no',man_Temp.emp_no AS 'manager_no',emp_Temp.salary AS 'emp_salary',man_Temp.salary AS 'manager_salary'
FROM
(SELECT
salaries.emp_no,salaries.salary,salaries.to_date,dept_emp.dept_no
FROM
salaries LEFT JOIN dept_emp
ON dept_emp.emp_no = salaries.emp_no
WHERE
salaries.emp_no NOT IN(
    SELECT
    emp_no
    FROM
    dept_manager
)) AS emp_Temp
,
(SELECT
salaries.emp_no,salaries.salary,salaries.to_date,dept_emp.dept_no
FROM
salaries LEFT JOIN dept_emp
ON dept_emp.emp_no = salaries.emp_no
WHERE salaries.emp_no IN(
    SELECT
    emp_no
    FROM
    dept_manager
)) AS man_Temp

WHERE
emp_Temp.salary > man_Temp.salary AND emp_Temp.to_date = '9999-01-01' AND man_Temp.to_date = '9999-01-01' AND  emp_Temp.dept_no = man_Temp.dept_no