Leetcode570. 至少有5名直接下属的经理（中等）

题目
Employee 表包含所有员工和他们的经理。每个员工都有一个 Id，并且还有一列是经理的 Id。

+------+----------+-----------+----------+
|Id    |Name       |Department |ManagerId |
+------+----------+-----------+----------+
|101   |John       |A           |null      |
|102   |Dan       |A           |101       |
|103   |James       |A           |101       |
|104   |Amy       |A           |101       |
|105   |Anne       |A           |101       |
|106   |Ron       |B           |101       |
+------+----------+-----------+----------+

给定 Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表，您的SQL查询应该返回：

+-------+
| Name  |
+-------+
| John  |
+-------+

注意:
没有人是自己的下属。

解答
根据Employee表可以统计每个经理的员工个数

select ManagerId
from Employee
group by ManagerId
having count(ManagerId) >= 5;

然后和主表连接即可

select E.Name
from Employee as E
join (select ManagerId
from Employee
group by ManagerId
having count(ManagerId) >= 5) as A
on E.Id = A.ManagerId

自连接也可

select E1.Name
from Employee as E1 
join Employee as E2 
on(E1.id = E2.managerid)
group by E1.id,E1.Name
having count(E1.id) >= 5

对每一个员工，用嵌套查询是否有五个下属即可。

select a.Name
from Employee as a
where 5<=(select count(b.Id) from Employee as b where b.ManagerId=a.Id)