题解 | #获取员工薪水比其manager薪水还高#

SELECT t2.emp_no as emp_no,
    t1.emp_no as manager_no,
    t2.salary as emp_salary,
    t1.salary as manager_salary
FROM (SELECT m.dept_no,m.emp_no,s.salary
            FROM dept_manager m
             JOIN salaries s USING(emp_no)
            WHERE s.to_date = '9999-01-01'
) t1 -- 领导信息
JOIN (SELECT e.dept_no,e.emp_no,s.salary
    FROM dept_emp e
        JOIN salaries s USING(emp_no)
    WHERE (dept_no,emp_no) NOT IN(
        SELECT dept_no,emp_no
        FROM dept_manager
    ) AND s.to_date = '9999-01-01'
) t2 -- 员工信息
on t1.dept_no = t2.dept_no
WHERE t1.salary < t2.salary

1、先查询部门领导的薪资

SELECT m.dept_no,m.emp_no,s.salary
            FROM dept_manager m
             JOIN salaries s USING(emp_no)
            WHERE s.to_date = '9999-01-01'

2、再查询不同部门员工的薪资

SELECT e.dept_no,e.emp_no,s.salary
    FROM dept_emp e
        JOIN salaries s USING(emp_no)
    WHERE (dept_no,emp_no) NOT IN(
        SELECT dept_no,emp_no
        FROM dept_manager
    ) AND s.to_date = '9999-01-01'

3、两表连接，判定员工薪资大于领导薪资的

SELECT t2.emp_no as emp_no,
    t1.emp_no as manager_no,
    t2.salary as emp_salary,
    t1.salary as manager_salary
FROM (SELECT m.dept_no,m.emp_no,s.salary
            FROM dept_manager m
             JOIN salaries s USING(emp_no)
            WHERE s.to_date = '9999-01-01'
) t1 -- 领导信息
JOIN (SELECT e.dept_no,e.emp_no,s.salary
    FROM dept_emp e
        JOIN salaries s USING(emp_no)
    WHERE (dept_no,emp_no) NOT IN(
        SELECT dept_no,emp_no
        FROM dept_manager
    ) AND s.to_date = '9999-01-01'
) t2 -- 员工信息
on t1.dept_no = t2.dept_no
WHERE t1.salary < t2.salary