获取每个部门中薪水最高的员工相关信息【sql练习题】

描述

有一个员工表dept_emp简况如下:

有一个薪水表salaries简况如下:

获取每个部门中薪水最高的员工相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:

示例1

输入：

drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');

输出：

dept_no|emp_no|salary
d001   |10001 |88958
d002   |10003 |92527

方法一：使用公共表表达式（CTE）和连接操作

-- 定义一个 CTE 名为 joined_data，用于将 dept_emp 表和 salaries 表进行连接
WITH joined_data AS (
    SELECT 
        dept_emp.emp_no,
        dept_emp.dept_no,
        salaries.salary
    FROM 
        dept_emp
    JOIN 
        salaries ON dept_emp.emp_no = salaries.emp_no
),
-- 定义另一个 CTE 名为 max_salaries，用于计算每个部门的最高工资
max_salaries AS (
    SELECT 
        dept_no,
        MAX(salary) AS max_salary
    FROM 
        joined_data
    GROUP BY 
        dept_no
)
-- 从 joined_data 中选取符合条件的记录
SELECT 
    joined_data.emp_no,
    joined_data.dept_no,
    joined_data.salary
FROM 
    joined_data
JOIN 
    max_salaries ON joined_data.dept_no = max_salaries.dept_no
WHERE 
    joined_data.salary = max_salaries.max_salary;

详细解释

1. joined_data CTE
   • 此 CTE 的作用是将 dept_emp 表和 salaries 表进行连接。通过 JOIN 关键字，以 emp_no 作为连接条件，将两个表中员工编号相同的记录组合在一起。
   • 从 dept_emp 表中选取员工编号 emp_no 和部门编号 dept_no，从 salaries 表中选取工资 salary。这样就得到了一个包含员工编号、部门编号和工资信息的临时结果集，为后续操作提供基础数据。
2. max_salaries CTE
   • 该 CTE 基于 joined_data 进行操作。使用 GROUP BY 子句按照部门编号 dept_no 对数据进行分组。
   • 对于每个分组，使用 MAX() 聚合函数计算该部门内的最高工资，并将其命名为 max_salary。此时得到的结果集包含每个部门编号及其对应的最高工资。
3. 主查询
   • 主查询将 joined_data 和 max_salaries 进行连接，连接条件是部门编号 dept_no 相等。
   • 通过 WHERE 子句筛选出 joined_data 中工资等于该部门最高工资 max_salary 的记录，最终返回这些记录的员工编号、部门编号和工资信息。

方法二：使用子查询和 IN 子句

SELECT 
    dept_emp.emp_no,
    dept_emp.dept_no,
    salaries.salary
FROM 
    dept_emp
JOIN 
    salaries ON dept_emp.emp_no = salaries.emp_no
WHERE 
    (dept_emp.dept_no, salaries.salary) IN (
        SELECT 
            dept_no,
            MAX(salary)
        FROM 
            dept_emp
        JOIN 
            salaries ON dept_emp.emp_no = salaries.emp_no
        GROUP BY 
            dept_no
    );

详细解释

1. 子查询
   • 子查询同样是将 dept_emp 表和 salaries 表通过 emp_no 进行连接，得到员工的基本信息。
   • 使用 GROUP BY 子句按照部门编号 dept_no 对连接后的结果集进行分组。
   • 对于每个分组，使用 MAX() 聚合函数计算该部门的最高工资。最终子查询返回一个包含部门编号和该部门最高工资的结果集。
2. 主查询
   • 主查询将 dept_emp 表和 salaries 表进行连接，获取员工的编号、部门编号和工资信息。
   • 通过 WHERE 子句中的 IN 子句进行筛选。(dept_emp.dept_no, salaries.salary) IN (...) 表示只有当主查询中某条记录的部门编号和工资组合与子查询结果集中的某一行的部门编号和最高工资组合相匹配时，该记录才会被选中。

方法三：使用窗口函数

SELECT 
    emp_no,
    dept_no,
    salary
FROM (
    SELECT 
        dept_emp.emp_no,
        dept_emp.dept_no,
        salaries.salary,
        RANK() OVER (PARTITION BY dept_emp.dept_no ORDER BY salaries.salary DESC) as salary_rank
    FROM 
        dept_emp
    JOIN 
        salaries ON dept_emp.emp_no = salaries.emp_no
) ranked
WHERE 
    salary_rank = 1;

详细解释

1. 子查询
   • 子查询将 dept_emp 表和 salaries 表通过 emp_no 进行连接，获取员工的编号、部门编号和工资信息。
   • 使用窗口函数 RANK() 对数据进行处理。PARTITION BY dept_emp.dept_no 表示将结果集按照部门编号进行分区，每个部门是一个独立的组。
   • ORDER BY salaries.salary DESC 表示在每个分区内，按照工资降序排列。RANK() 函数会为每个分区内的行赋予一个排名，工资最高的行排名为 1，依次类推。如果有多个行的工资相同，它们会获得相同的排名，下一个不同工资的行的排名会跳过相应的数量。
   • 排名结果存储在 salary_rank 列中，此时子查询返回一个包含员工编号、部门编号、工资和排名信息的结果集。
2. 主查询
   • 主查询从子查询结果集中筛选出 salary_rank 为 1 的记录，即每个部门中工资最高的员工信息。最终返回这些员工的编号、部门编号和工资。

总结

这三种方法都能实现获取每个部门中薪水最高的员工相关信息的目的，但在性能和代码可读性上可能会有所差异。方法三使用窗口函数通常在处理大数据量时性能较好，且代码逻辑相对简洁直观。

扩展

RANK() 是一种窗口函数，主要用于在查询结果集中对行进行排名。下面详细介绍它的用法、原理以及与示例结合进行理解。

基本语法

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

• PARTITION BY：可选子句，用于将结果集划分为多个分区（组）。RANK() 函数会在每个分区内独立地进行排名。如果省略该子句，RANK() 函数会将整个结果集视为一个分区。
• ORDER BY：必选子句，用于指定排名的排序规则，可以按照一个或多个列进行排序，并且可以指定升序（ASC，默认）或降序（DESC）。

排名规则

• 当排序值相同时，RANK() 会为这些相同的值赋予相同的排名，并且下一个不同的值的排名会跳过相应的数量。例如，如果有三个值并列第 2 名，那么下一个值的排名将是第 5 名。

示例分析

结合前面获取每个部门中薪水最高的员工相关信息的 SQL 代码来理解 RANK() 函数：

SELECT 
    emp_no,
    dept_no,
    salary
FROM (
    SELECT 
        dept_emp.emp_no,
        dept_emp.dept_no,
        salaries.salary,
        RANK() OVER (PARTITION BY dept_emp.dept_no ORDER BY salaries.salary DESC) as salary_rank
    FROM 
        dept_emp
    JOIN 
        salaries ON dept_emp.emp_no = salaries.emp_no
) ranked
WHERE 
    salary_rank = 1;

步骤解释

1. 连接表：
   • 通过 JOIN 操作将 dept_emp 表和 salaries 表根据 emp_no 列连接起来，得到一个包含员工编号、部门编号和工资的结果集。
2. 使用 RANK() 函数进行排名：
   • PARTITION BY dept_emp.dept_no：将结果集按照部门编号进行分区，即每个部门是一个独立的组。
   • ORDER BY salaries.salary DESC：在每个分区内，按照工资降序排列。
   • RANK() OVER (...) as salary_rank：为每个分区内的行计算排名，并将排名结果存储在 salary_rank 列中。
3. 筛选排名为 1 的记录：
   • 外层查询通过 WHERE salary_rank = 1 筛选出每个部门中工资排名为第 1 的员工信息。

具体示例数据演示

假设我们有以下数据：

执行 RANK() 函数后的中间结果如下：

最后通过 WHERE salary_rank = 1 筛选出的结果为：

通过以上示例，你可以更清楚地理解 RANK() 函数的工作原理和使用方法。