【每日SQL打卡】​​​​​​​​​​​​​​​DAY 5丨至少有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  |
+-------+

1、思路：
e1作为经理，e2员工
e1.Id = e2.ManagerID 经理管的员工
e1.Department = e2.Department 同部门
group by e1.name 按照经理进行分组
having count(*)>4 统计大于4的

select
	e1.Name
from 
	Employee e1,
	Employee e2
where 
	e1.Id = e2.ManagerID,
	e1.Department  = e2.Department 
	group by e1.name
	having count(*)>4

上面的思路进化后的效果如下：

SELECT Name FROM Employee WHERE Id IN 
(SELECT 
	ManagerId 
FROM 
	Employee 
GROUP BY ManagerId 
HAVING count(1) >= 5);