【LeetCode1270. 向公司CEO汇报工作的所有人】[MySQL with recursive/PostgreSQL with recursive/T-SQL with as/Oracle connect by/Pandas]通过递归找到某节点所有的后代

目录

• 题目地址
• 代码
  • • MySQL/PostgreSQL
    • Oracle
    • T-SQL
    • Pandas

题目地址

https://leetcode.cn/problems/all-people-report-to-the-given-manager/description/

代码

MySQL/PostgreSQL

WITH RECURSIVE cte_subordinates AS (
    -- 基础情况: 找到直接下属
    SELECT employee_id 
    FROM Employees 
    WHERE manager_id = 1
    UNION
    -- 递归情况: 找到下属的下属
    SELECT t.employee_id 
    FROM Employees  t
    INNER JOIN cte_subordinates s ON t.manager_id = s.employee_id
)
SELECT employee_id FROM cte_subordinates
where employee_id <>1

Oracle

这题其实是Oracle connect by例题。

SELECT employee_id
FROM Employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id = 1
AND employee_id <> 1 -- Exclude the manager

T-SQL

递归终止条件不知道加在哪，这里限制了递归深度。

WITH cte_subordinates AS (
    -- Base case: find direct subordinates
    SELECT employee_id, 1 AS recursion_level
    FROM Employees 
    WHERE manager_id = 1
    UNION ALL
    -- Recursive case: find subordinates of subordinates
    SELECT e.employee_id, s.recursion_level + 1
    FROM Employees e
    INNER JOIN cte_subordinates s ON e.manager_id = s.employee_id
    WHERE s.recursion_level < 100 -- Explicit termination condition
)
-- Final SELECT to return the results from the CTE
SELECT distinct employee_id 
FROM cte_subordinates
WHERE employee_id <> 1
OPTION (MAXRECURSION 100);

Pandas

采取一种递归写法

import pandas as pd

def find_reporting_people(employees: pd.DataFrame) -> pd.DataFrame:

    def find_subordinates(employees: pd.DataFrame, manager_id: int) -> list:
        # Base case: find direct subordinates of the given manager
        direct_subordinates = employees[employees['manager_id'] == manager_id]['employee_id'].tolist()

        # Recursively find each subordinate's subordinates
        all_subordinates = []
        for subordinate_id in direct_subordinates:
            # Avoid adding the manager himself/herself
            if subordinate_id != manager_id:
                all_subordinates.append(subordinate_id)
                # Recursive case: find subordinates of the current subordinate
                all_subordinates.extend(find_subordinates(employees, subordinate_id))

        return all_subordinates

    # Starting recursive search for subordinates with manager_id = 1
    subordinates_list = find_subordinates(employees=employees, manager_id=1)
    # Convert the list to a DataFrame
    return pd.DataFrame({'employee_id': subordinates_list})