至少有5名直接下属的经理

570. 至少有5名直接下属的经理

表: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| department  | varchar |
| managerId   | int     |
+-------------+---------+
Id是该表的主键列。
该表的每一行都表示雇员的名字、他们的部门和他们的经理的id。
如果managerId为空，则该员工没有经理。
没有员工会成为自己的管理者。

编写一个SQL查询，查询至少有5名直接下属的经理。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Employee 表:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | None      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+
输出: 
+------+
| name |
+------+
| John |
+------+

原站题解

去查看

上次编辑到这里，代码来自缓存点击恢复默认模板

# Write your MySQL query statement below

pythondata 解法, 执行用时: 288 ms, 内存消耗: 60.5 MB, 提交时间: 2023-08-09 15:39:37

import pandas as pd

def find_managers(employee: pd.DataFrame) -> pd.DataFrame:
    df = employee.groupby('managerId').size().reset_index(name='count')
    df = df[df['count'] >= 5]
    managers_info = pd.merge(df, employee, left_on='managerId', right_on='id', how='inner')
    return managers_info[['name']]

pythondata 解法, 执行用时: 252 ms, 内存消耗: 60.2 MB, 提交时间: 2023-08-09 15:38:42

import pandas as pd

def find_managers(employee: pd.DataFrame) -> pd.DataFrame:
    # 统计每个经理的直接下属数量
    subordinate_count = employee.groupby('managerId')['id'].count()
    
    # 找出直接下属数量大于等于5的经理
    managers_with_5_subordinates = subordinate_count[subordinate_count >= 5].index
    
    # 使用这些经理的id来获取经理姓名
    result = employee[employee['id'].isin(managers_with_5_subordinates)]['name']
    
    # 将 Series 转换为 DataFrame
    result_df = result.to_frame(name='name')
    
    return result_df

mysql 解法, 执行用时: 186 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:21:58

# Write your MySQL query statement below
select Name 
from Employee
where Id in
(
    select distinct ManagerId
    from Employee
    group by ManagerID
    having count(ManagerID)>=5
)

mysql 解法, 执行用时: 190 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:21:43

# Write your MySQL query statement below
SELECT
    Name
FROM
    Employee AS t1 JOIN
    (SELECT
        ManagerId
    FROM
        Employee
    GROUP BY ManagerId
    HAVING COUNT(ManagerId) >= 5) AS t2
    ON t1.Id = t2.ManagerId
;

上一题

下一题

详情