LeetCode_sql刷題(3482.分析組織層級)

題目描述：3482. 分析組織層級 - 力扣（LeetCode）

表：Employees
+----------------+---------+
| Column Name    | Type    | 
+----------------+---------+
| employee_id    | int     |
| employee_name  | varchar |
| manager_id     | int     |
| salary         | int     |
| department     | varchar |
+----------------+----------+
employee_id 是這張表的唯一主鍵。
每一行包含關于一名員工的信息，包括他們的 ID，姓名，他們經理的 ID，薪水和部門。
頂級經理（CEO）的 manager_id 是空的。
編寫一個解決方案來分析組織層級并回答下列問題：

層級：對于每名員工，確定他們在組織中的層級（CEO 層級為?1，CEO 的直接下屬員工層級為?2，以此類推）。
團隊大小：對于每個是經理的員工，計算他們手下的（直接或間接下屬）總員工數。
薪資預算：對于每個經理，計算他們控制的總薪資預算（所有手下員工的工資總和，包括間接下屬，加上自己的工資）。

返回結果表以?層級?升序?排序，然后以預算?降序?排序，最后以?employee_name?升序?排序。

結果格式如下所示。

示例：

輸入：

Employees 表：
+-------------+---------------+------------+--------+-------------+
| employee_id | employee_name | manager_id | salary | department  |
+-------------+---------------+------------+--------+-------------+
| 1           | Alice         | null       | 12000  | Executive   |
| 2           | Bob           | 1          | 10000  | Sales       |
| 3           | Charlie       | 1          | 10000  | Engineering |
| 4           | David         | 2          | 7500   | Sales       |
| 5           | Eva           | 2          | 7500   | Sales       |
| 6           | Frank         | 3          | 9000   | Engineering |
| 7           | Grace         | 3          | 8500   | Engineering |
| 8           | Hank          | 4          | 6000   | Sales       |
| 9           | Ivy           | 6          | 7000   | Engineering |
| 10          | Judy          | 6          | 7000   | Engineering |
+-------------+---------------+------------+--------+-------------+
輸出：
+-------------+---------------+-------+-----------+--------+
| employee_id | employee_name | level | team_size | budget |
+-------------+---------------+-------+-----------+--------+
| 1           | Alice         | 1     | 9         | 84500  |
| 3           | Charlie       | 2     | 4         | 41500  |
| 2           | Bob           | 2     | 3         | 31000  |
| 6           | Frank         | 3     | 2         | 23000  |
| 4           | David         | 3     | 1         | 13500  |
| 7           | Grace         | 3     | 0         | 8500   |
| 5           | Eva           | 3     | 0         | 7500   |
| 9           | Ivy           | 4     | 0         | 7000   |
| 10          | Judy          | 4     | 0         | 7000   |
| 8           | Hank          | 4     | 0         | 6000   |
+-------------+---------------+-------+-----------+--------+
解釋：

組織結構：
Alice（ID：1）是 CEO（層級 1）沒有經理。
Bob（ID：2）和?Charlie（ID：3）是?Alice 的直接下屬（層級 2）
David（ID：4），Eva（ID：5）從屬于?Bob，而 Frank（ID：6）和 Grace（ID：7）從屬于?Charlie（層級 3）
Hank（ID：8）從屬于?David，而 Ivy（ID：9）和?Judy（ID：10）從屬于?Frank（層級 4）

層級計算：
CEO（Alice）層級為 1
每個后續的管理層級都會使層級數加 1

團隊大小計算：
Alice 手下有 9 個員工（除她以外的整個公司）
Bob 手下有 3 個員工（David，Eva 和 Hank）
Charlie 手下有 4 個員工（Frank，Grace，Ivy 和 Judy）
David 手下有 1 個員工（Hank）
Frank 手下有 2 個員工（Ivy 和 Judy）
Eva，Grace，Hank，Ivy 和 Judy 沒有直接下屬（team_size = 0）

預算計算：
Alice 的預算：她的工資（12000）+ 所有員工的工資（72500）= 84500
Charlie 的預算：他的工資（10000）+ Frank 的預算（23000）+ Grace 的工資（8500）= 41500
Bob 的預算：他的工資 (10000) + David?的預算（13500）+ Eva?的工資（7500）= 31000
Frank 的預算：他的工資 (9000) + Ivy 的工資（7000）+ Judy?的工資（7000）= 23000
David 的預算：他的工資 (7500) + Hank 的工資（6000）= 13500
沒有直接下屬的員工的預算等于他們自己的工資。

注意：

結果先以層級升序排序
在同一層級內，員工按預算降序排序，然后按姓名升序排序

數據準備

CREATE TABLE if not exists Employees (employee_id INT,employee_name VARCHAR(100),manager_id INT,salary INT,department VARCHAR(50)
)
Truncate table Employees
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('1', 'Alice', NULL, '12000', 'Executive')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('2', 'Bob', '1', '10000', 'Sales')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('3', 'Charlie', '1', '10000', 'Engineering')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('4', 'David', '2', '7500', 'Sales')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('5', 'Eva', '2', '7500', 'Sales')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('6', 'Frank', '3', '9000', 'Engineering')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('7', 'Grace', '3', '8500', 'Engineering')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('8', 'Hank', '4', '6000', 'Sales')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('9', 'Ivy', '6', '7000', 'Engineering')
insert into Employees (employee_id, employee_name, manager_id, salary, department) values ('10', 'Judy', '6', '7000', 'Engineering')

分析

①使用recursive進行遞歸查詢通過引用 CTE 自身不斷生成新的結果集，直到滿足終止條件主表 employee_id 和子表manager_id 連接條件直到employee_id遍歷完? 通過子表employee_id 記錄路徑，level記錄層級?
with recursive t1 as (select employee_id,employee_name,1                         as level,CAST(employee_id AS CHAR) as path,salary,department,manager_idfrom Employeeswhere manager_id is nullunionselect t2.employee_id,t2.employee_name,1 + t1.level,concat(t1.path, ',', t2.employee_id) as path,t2.salary,t2.department,t2.manager_idfrom Employees t2join t1on t2.manager_id = t1.employee_id)
select * from t1
②根據路徑展開主表和子表將主表employee_id下的所有子員工都展開
select e.employee_id as m_id, e2.employee_id, e2.salaryfrom t1 ejoin t1 e2 on e2.path like concat(e.path, '%')
③根據主employee_id? 計算team_size團隊人數和budget預算
select m_id, (count(m_id) - 1) as team_size, sum(salary) as budgetfrom t3group by m_id
④將結果進行連接排序

代碼

with recursive t1 as (select employee_id,employee_name,1                         as level,CAST(employee_id AS CHAR) as path,salary,department,manager_idfrom Employeeswhere manager_id is nullunionselect t2.employee_id,t2.employee_name,1 + t1.level,concat(t1.path, ',', t2.employee_id) as path,t2.salary,t2.department,t2.manager_idfrom Employees t2join t1on t2.manager_id = t1.employee_id)
# select * from t1, t3 as (select e.employee_id as m_id, e2.employee_id, e2.salaryfrom t1 ejoin t1 e2 on e2.path like concat(e.path, '%')), t4 as (select m_id, (count(m_id) - 1) as team_size, sum(salary) as budgetfrom t3group by m_id)
select employee_id, employee_name, level, team_size, budget
from t1left join t4 on employee_id = m_id
order by level, budget desc, employee_name;-- 合并
with recursive employee as(select employee_id, employee_name, manager_id, salary, 1 as level, cast(employee_id as char) as pathfrom Employeeswhere manager_id is nullunionselect e2.employee_id,e2.employee_name,e2.manager_id,e2.salary,1 + employee.level,concat(employee.path, ',', e2.employee_id)from Employees e2join employee one2.manager_id = employee.employee_id)
# select * from
, t3 as(select e1.employee_id ,e2.employee_id as emp,e1.employee_name,e1.level,e2.salary from employee e1 left join employee e2 on  e2.path like concat(e1.path,'%')
)
select employee_id,employee_name,level,(count(employee_id)-1) as team_size,sum(salary)'budget'  from t3
group by employee_id, employee_name,level
order by level,budget desc,employee_name
;

總結

①遞歸查詢基本結構
WITH RECURSIVE cte_name (column_list) AS (-- 錨成員（初始查詢）SELECT ...UNION [ALL]-- 遞歸成員（遞歸查詢）SELECT ...
)
SELECT * FROM cte_name;
錨成員：返回初始結果集。
遞歸成員：通過引用 CTE 自身不斷生成新的結果集，直到滿足終止條件。

②執行順序分析

遞歸查詢的執行遵循?迭代模型，步驟如下：

步驟 1：執行錨成員

錨查詢首先執行，生成遞歸的基礎結果集（R0）。

步驟 2：執行遞歸成員

將遞歸成員應用于上一次迭代的結果集（初始為 R0），生成新的結果集（R1）。
去重處理：如果使用?UNION（而非?UNION ALL），會自動刪除重復行。

步驟 3：檢查終止條件

如果新生成的結果集（R1）為空，遞歸終止。
否則，將 R1 作為下一次迭代的輸入，重復步驟 2。

步驟 4：合并所有結果

遞歸終止后，將所有迭代的結果集合并（隱式使用?UNION ALL），返回最終結果。

③ 強轉為字符串才可以進行拼接
 cast(employee_id as char)