【LeetCode 3236. 首席执行官下属层级】[mysql/pgsql/t-sql/oracle] with recursive标记树所有节点的高度

目录

• 题目地址
• 代码
  • mysql
  • pgsql
  • t-sql
  • oracle

题目地址

https://leetcode.cn/problems/ceo-subordinate-hierarchy/description/

代码

mysql

WITH RECURSIVE EmployeeHierarchy AS (
    -- Base case: Start with employees who have no manager (root, level 0)
    SELECT employee_id, employee_name, manager_id, salary,
    0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: Join with the 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
)

SELECT employee_id  as subordinate_id ,
employee_name as subordinate_name ,
level as hierarchy_level,
salary-root_salary as salary_difference 
FROM (SELECT *, max(case when level=0  then salary end) over() as root_salary from EmployeeHierarchy )temp1
where level>0
ORDER BY level, employee_id

pgsql

WITH RECURSIVE EmployeeHierarchy AS (
    -- Base case: Start with employees who have no manager (root, level 0)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        salary,
        0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: Join with the 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
)

SELECT 
    employee_id AS subordinate_id,
    employee_name AS subordinate_name,
    level AS hierarchy_level,
    salary - root_salary AS salary_difference 
FROM (
    SELECT 
        *,
        MAX(CASE WHEN level = 0 THEN salary END) OVER () AS root_salary 
    FROM EmployeeHierarchy 
) temp1
WHERE level > 0
ORDER BY level, employee_id;

t-sql

WITH EmployeeHierarchy AS (
    -- Base case: Start with employees who have no manager (root, level 0)
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        salary,
        0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: Join with the 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
)

SELECT 
    employee_id AS subordinate_id,
    employee_name AS subordinate_name,
    level AS hierarchy_level,
    salary - root_salary AS salary_difference
FROM (
    SELECT *,
        MAX(CASE WHEN level = 0 THEN salary END) OVER () AS root_salary 
    FROM EmployeeHierarchy
) temp1
WHERE level > 0
ORDER BY level, employee_id;

oracle

WITH EmployeeHierarchy (employee_id, employee_name, manager_id, salary, lvl) AS (
    -- Base case: Start with employees who have no manager
    SELECT employee_id, employee_name, manager_id, salary, 0
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: Join with the next level of employees
    SELECT e.employee_id, e.employee_name, e.manager_id, e.salary, eh.lvl + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
),
temp1 AS (
    SELECT eh.*, 
           MAX(CASE WHEN lvl = 0 THEN salary ELSE 0 END) OVER () as root_salary 
    FROM EmployeeHierarchy eh
)

-- -- 这么写不行，会报错ORA-00923: FROM keyword not found where expected
-- temp1 AS (
--     SELECT *, 
--            MAX(CASE WHEN lvl = 0 THEN salary ELSE 0 END) OVER () as root_salary 
--     FROM EmployeeHierarchy 
-- )

SELECT 
    employee_id AS subordinate_id,
    employee_name AS subordinate_name,
    lvl AS hierarchy_level,
    salary - root_salary AS salary_difference
FROM temp1
WHERE lvl > 0
ORDER BY hierarchy_level, employee_id;