牛客网：SQL习题（三）

停更了3天，在学习机器学习，终于从里面闭关出来了，继续更新SQL题。

SQL222 对所有员工的薪水按照salary降序进行1-N的排名

题目：
薪资表 salaries

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01
10004	72527	2001-12-01	9999-01-01

员工薪水排名

emp_no	salary	t_rank
10001	88958	1
10002	72527	2
10004	72527	2
10003	43311	3
解题思路：

根据本题的要求拆解答案过程：

1. 按照 salary 降序排名：即需要用到窗口函数 先按
2. salary 排名后再按照 emp_no 升序排序
   可以观察到，salary 和 emp_no 并不在同一张表里，所以需要用到内连接

SELECT
    emp_no,
    salary,
    DENSE_RANK() OVER (
        ORDER BY
            salary DESC
    ) AS t_rank
FROM
    salaries
ORDER BY
    t_rank ASC,
    emp_no ASC;

SQL223 获取所有非manager员工当前的薪水情况

题目：
薪水表 salaries 概况

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01
10004	72527	2001-12-01	9999-01-01

薪水排名

对所有员工的薪水按照 salary 降序先进行 1-N 的排名，如果 salary 相同，再按照 emp_no 升序排列：

emp_no	salary	t_rank
10001	88958	1
10002	72527	2
10004	72527	2
10003	43311	3

解题思路：

根据任务描述，可得我们需要查找的是非 manager 员工，这一题与SQL 202其实有一些联系。我们要找非 manager 员工，从表里我们可以得到，需要用所有员工减去 manager 员工，步骤如下：

1. 根据 emp_no 字段内连接部门经理表和员工关系表来筛选非 manger 的员工，形成临时表
2. 针对临时表，结合薪水表，拿到我们需要的信息

SELECT
    d.dept_no,
    e.emp_no,
    s.salary
FROM
    employees AS e
    INNER JOIN salaries AS s ON e.emp_no = s.emp_no
    INNER JOIN (
        SELECT
            de.emp_no,
            de.dept_no
        FROM
            dept_emp AS de
            INNER JOIN dept_manager AS dm ON de.dept_no = dm.dept_no
        WHERE
            de.emp_no != dm.emp_no
    ) AS d ON d.emp_no = e.emp_no

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

题目：

有一个，部门关系表dept_emp简况如下:

部门表部门员工概况

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01

部门经理表 dept_manager 概况

dept_no	emp_no	from_date	to_date
d001	10002	1996-08-03	9999-01-01

薪水表 salaries 概况

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	1996-08-03	9999-01-01

任务描述

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

输出示例

emp_no	manager_no	emp_salary	manager_salary
10001	10002	88958	72527

解题思路：

拆解本题的任务描述，我们需要获取的是当前薪水比其manager薪水还要高的员工。

本题和上一题有着异曲同工之妙。解题步骤如下：

1. 找到员工的薪水，形成临时表1
2. 找到 manager 的薪水，形成临时表2
3. 将临时表1和临时表2根据 dept_no 字段内连接，并补全所需信息。

SELECT da1.emp_no, da2.emp_no AS manager_no, da1.salary AS emp_salary, da2.salary AS manager_salary
FROM
    (SELECT de.emp_no, de.dept_no, sa.salary
    FROM dept_emp AS de
    INNER JOIN salaries AS sa ON de.emp_no = sa.emp_no) AS da1
INNER JOIN 
    (SELECT dm.emp_no, dm.dept_no, sa.salary
    FROM dept_manager AS dm
    INNER JOIN salaries AS sa ON dm.emp_no = sa.emp_no) AS da2 
    ON da1.dept_no =da2.dept_no
WHERE da1.salary > da2.salary AND da1.emp_no != da2.emp_no

---

题外话

关注我的微信公众号“曼珠沙华的生活”，将不定期更新
我建了一个关于失业的算法工程师的群，可以公众号后台回复“进群”，欢迎大家入群交流！（无广仅交流！）
关于SQL习题的智能体，我的链接可以给大家分享一下，欢迎使用：https://doubao.com/bot/u0Z9J2xn