3236. 首席执行官下属层级 🔒

题目描述

表：Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
| manager_id    | int     |
| salary        | int     |
+---------------+---------+
employee_id 是这张表的唯一标识符。
manager_id 是 employee_id 对应员工的经理。首席执行官的 manager_id 为 NULL。

编写一个解决方案来找到首席执行官的下属（直接和 非直接），以及他们在 等级制度中的级别 以及与首席执行官的 薪资差异。结果应该包含下面的列：

查询结果格式如下所示。

subordinate_id：下属的 employee_id。
subordinate_name：下属的名字。
hierarchy_level：下属在等级制度中的级别（1 表示直接下属，2 表示 他们的直接下属，以此类推。）
salary_difference：下属与首席执行官的薪资差异。

返回结果表以 hierarchy_level 升序排序，然后按 subordinate_id 升序排序。

查询格式如下所示。

示例：

输入：

Employees 表：

+-------------+----------------+------------+---------+
| employee_id | employee_name  | manager_id | salary  |
+-------------+----------------+------------+---------+
| 1           | Alice          | NULL       | 150000  |
| 2           | Bob            | 1          | 120000  |
| 3           | Charlie        | 1          | 110000  |
| 4           | David          | 2          | 105000  |
| 5           | Eve            | 2          | 100000  |
| 6           | Frank          | 3          | 95000   |
| 7           | Grace          | 3          | 98000   |
| 8           | Helen          | 5          | 90000   |
+-------------+----------------+------------+---------+

输出：

+----------------+------------------+------------------+-------------------+
| subordinate_id | subordinate_name | hierarchy_level  | salary_difference |
+----------------+------------------+------------------+-------------------+
| 2              | Bob              | 1                | -30000            |
| 3              | Charlie          | 1                | -40000            |
| 4              | David            | 2                | -45000            |
| 5              | Eve              | 2                | -50000            |
| 6              | Frank            | 2                | -55000            |
| 7              | Grace            | 2                | -52000            |
| 8              | Helen            | 3                | -60000            |
+----------------+------------------+------------------+-------------------+

解释：

Bob 和 Charlie 是 Alice 的直接下属（首席执行官）因此，hierarchy_level 为 1。
David 和 Eve 下属于 Bob，而 Frank 和 Grace 下属于 Charlie，因此他们是二级下属（hierarchy_level 为 2）。
Helen 下属于 Eve，因此 Helen 为三级下属（hierarchy_level 为 3）。
薪资差异是相对于 Alice 的薪资 150000 计算的。
结果先以 hierarchy_level 升序排序，然后以 subordinate_id 升序排序。

注意：输出表先以 hierarchy_level 升序排序，然后以 subordinate_id 升序排序。

解法

方法一：递归 CTE + 连接

首先，我们使用递归 CTE 计算出每个员工的层级，其中 CEO 的层级为 0，将 employee_id、employee_name、hierarchy_level、manager_id 和 salary 保存到临时表 T 中。

然后，我们查询出 CEO 的薪资，将其保存到临时表 P 中。

最后，我们连接 T 和 P 表，计算出每个下属的薪资差异，并按照 hierarchy_level 和 subordinate_id 进行排序。

MySQL

# Write your MySQL query statement below
WITH RECURSIVE
    T AS (
        SELECT
            employee_id,
            employee_name,
            0 AS hierarchy_level,
            manager_id,
            salary
        FROM Employees
        WHERE manager_id IS NULL
        UNION ALL
        SELECT
            e.employee_id,
            e.employee_name,
            hierarchy_level + 1 AS hierarchy_level,
            e.manager_id,
            e.salary
        FROM
            T t
            JOIN Employees e ON t.employee_id = e.manager_id
    ),
    P AS (
        SELECT salary
        FROM Employees
        WHERE manager_id IS NULL
    )
SELECT
    employee_id subordinate_id,
    employee_name subordinate_name,
    hierarchy_level,
    t.salary - p.salary salary_difference
FROM
    T t
    JOIN P p
WHERE hierarchy_level != 0
ORDER BY 3, 1;

3236. 首席执行官下属层级 🔒

题目描述

解法

方法一：递归 CTE + 连接

评论