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

 # 思路：先获取经理编号，如果员工编号不等于经理编号且员工编号的工资大于经理编号的工资，输出员工编号、经理编号、员工工资、经理工资。
# 难点：如何比较员工编号的工资大于经理编号的工资呢
# select
#     de.emp_no as emp_no,
#     dm.emp_no as manager_no,
#     s.salary as emp_salary,
#     (s.salary where de.emp_no = dm.emp_no) as manager_salary,
# from
#     dept_emp de
#     join dept_manager dm on de.dept_no = dm.dept_no
#     join salaries s on de.emp_no = s.emp_no
# where emp_no != manager_no and emp_salary > manager_salary;
SELECT
    de.emp_no AS emp_no,
    dm.emp_no AS manager_no,
    s.salary AS emp_salary,
    (
        SELECT
            s1.salary
        FROM
            salaries s1
        WHERE
            s1.emp_no = dm.emp_no
    ) AS manager_salary
FROM
    dept_emp de
    JOIN dept_manager dm ON de.dept_no = dm.dept_no
    JOIN salaries s ON de.emp_no = s.emp_no
WHERE
    de.emp_no != dm.emp_no
    AND s.salary > (
        SELECT
            s1.salary
        FROM
            salaries s1
        WHERE
            s1.emp_no = dm.emp_no
    );

这道题是获取员工其当前的薪水比其manager当前薪水还高的相关信息，第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary，第四列给该员工对应的manager当前的薪水manager_salary

三个关键点：

1.先将dept_emp表和dept_manager表按照部门编号dept_no相等连接，再通过员工编号emp_no和salaries表连接成一张新表

FROM
    dept_emp de
    JOIN dept_manager dm ON de.dept_no = dm.dept_no
    JOIN salaries s ON de.emp_no = s.emp_no

2.建立包含员工编号emp_no、经理编号manager_no、员工工资emp_salary、经理工资manager_salary四列的临时表

SELECT
    de.emp_no AS emp_no,
    dm.emp_no AS manager_no,
    s.salary AS emp_salary,
    (
        SELECT
            s1.salary
        FROM
            salaries s1
        WHERE
            s1.emp_no = dm.emp_no
    ) AS manager_salary

3.获取当前的薪水比其manager当前薪水还高员工的员工编号、经理编号、员工工资、经理工资信息，设置where条件

WHERE
    de.emp_no != dm.emp_no
    AND s.salary > (
        SELECT
            s1.salary
        FROM
            salaries s1
        WHERE
            s1.emp_no = dm.emp_no
    );

完整代码如下：

SELECT
    de.emp_no AS emp_no,
    dm.emp_no AS manager_no,
    s.salary AS emp_salary,
    (
        SELECT
            s1.salary
        FROM
            salaries s1
        WHERE
            s1.emp_no = dm.emp_no
    ) AS manager_salary
FROM
    dept_emp de
    JOIN dept_manager dm ON de.dept_no = dm.dept_no
    JOIN salaries s ON de.emp_no = s.emp_no
WHERE
    de.emp_no != dm.emp_no
    AND s.salary > (
        SELECT
            s1.salary
        FROM
            salaries s1
        WHERE
            s1.emp_no = dm.emp_no
    );

官方题解如下：

select
    d.emp_no,
    dm.emp_no as manager_no,
    s1.salary as emp_salary,
    s2.salary as manager_salary
from
    dept_emp d
    join dept_manager dm on dm.dept_no = d.dept_no
    join salaries s1 on s1.emp_no = d.emp_no
    join salaries s2 on s2.emp_no = dm.emp_no
where
    s1.salary > s2.salary;

官方题解相对于个人题解有了两个地方的优化，是通过两次join连接salaries表来实现对员工工资和经理工资分离成两列，然后进行比较；where条件去掉了多余的比较条件de.emp_no != dm.emp_no，因为工资不相等，就表明编号一定不相等。

完结