【LeetCode 3482. 分析组织层级】SQL处理树上递归问题

目录

• 题目地址
• 代码
  • mysql

题目地址

https://leetcode.cn/problems/analyze-organization-hierarchy/description/

代码

Grok 3.0给出的代码，简单修改后即可使用。
执行用时563 ms，击败11.76%

mysql

WITH RECURSIVE EmployeeHierarchy AS (
    -- Base case: Employees with no manager (top level)
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        salary,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: Join with next level of employees
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        e.salary,
        eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
,
FinalHierarchy as (
    SELECT 
    eh.employee_id,
    eh.employee_name,
    eh.manager_id,
    eh.salary,
    eh.level,
    -- Calculate total salary including self and all subordinates
    (SELECT SUM(e2.salary)
     FROM employees e2
     WHERE e2.employee_id = eh.employee_id
     OR EXISTS (
         WITH RECURSIVE Subordinates AS (
             SELECT employee_id
             FROM employees
             WHERE manager_id = eh.employee_id
             UNION ALL
             SELECT e3.employee_id
             FROM employees e3
             INNER JOIN Subordinates s ON e3.manager_id = s.employee_id
         )
         SELECT 1
         FROM Subordinates s
         WHERE s.employee_id = e2.employee_id
     )) AS total_salary,
    -- Calculate total number including self and all subordinates
    (SELECT COUNT(*)
     FROM employees e2
     WHERE e2.employee_id = eh.employee_id
     OR EXISTS (
         WITH RECURSIVE Subordinates AS (
             SELECT employee_id
             FROM employees
             WHERE manager_id = eh.employee_id
             UNION ALL
             SELECT e3.employee_id
             FROM employees e3
             INNER JOIN Subordinates s ON e3.manager_id = s.employee_id
         )
         SELECT 1
         FROM Subordinates s
         WHERE s.employee_id = e2.employee_id
     )) AS total_number
FROM EmployeeHierarchy eh
)

SELECT 
employee_id,  employee_name,  level, total_number-1 as  team_size , total_salary as  budget
FROM FinalHierarchy
ORDER BY level asc, budget desc, employee_name  asc;