README.md

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

English Version

题目描述

表: Employee

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

 

查询至少有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 |
+------+

解法

SQL

# Write your MySQL query statement below
SELECT
    name
FROM
    Employee AS e1
    JOIN (
        SELECT
            managerId
        FROM Employee
        WHERE managerId IS NOT NULL
        GROUP BY managerId
        HAVING count(1) >= 5
    ) AS e2
        ON e1.id = e2.managerId;

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            managerId,
            count(1) OVER (PARTITION BY managerId) AS cnt
        FROM Employee
    )
SELECT DISTINCT name
FROM
    Employee AS e
    JOIN T AS t ON e.id = t.managerId
WHERE cnt >= 5;