需求：Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。

展示效果：

Name
John

 

1 Create table If Not Exists Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int);
2 
3 insert into Employee (Id, Name, Department, ManagerId) values (101, 'John', 'A', null);
4 insert into Employee (Id, Name, Department, ManagerId) values (102, 'Dan', 'A', 101);
5 insert into Employee (Id, Name, Department, ManagerId) values (103, 'James', 'A', 101);
6 insert into Employee (Id, Name, Department, ManagerId) values (104, 'Amy', 'A', 101);
7 insert into Employee (Id, Name, Department, ManagerId) values (105, 'Anne', 'A', 101);
8 insert into Employee (Id, Name, Department, ManagerId) values (106, 'Ron', 'B', 101);

最终SQL:

 1 SELECT
 2     Name
 3 FROM
 4     Employee AS t1 
 5 JOIN 
 6    (SELECT
 7         ManagerId
 8     FROM
 9         Employee
10     GROUP BY 
11         ManagerId
12     HAVING
13         COUNT(ManagerId) >= 5
14     ) AS t2
15 ON  
16     t1.Id = t2.ManagerId;