570 至少有5名直接下属的经理
SQL架构
Create table If Not Exists Employee_570 (Id int, Name varchar(255), Department varchar(255), ManagerId int);
Truncate table Employee_570;
insert into Employee_570 (Id, Name, Department, ManagerId) values ('101', 'John', 'A', null);
insert into Employee_570 (Id, Name, Department, ManagerId) values ('102', 'Dan', 'A', '101');
insert into Employee_570 (Id, Name, Department, ManagerId) values ('103', 'James', 'A', '101');
insert into Employee_570 (Id, Name, Department, ManagerId) values ('104', 'Amy', 'A', '101');
insert into Employee_570 (Id, Name, Department, ManagerId) values ('105', 'Anne', 'A', '101');
insert into Employee_570 (Id, Name, Department, ManagerId) values ('106', 'Ron', 'B', '101');
/*
Employee_570 表包含所有员工和他们的经理。每个员工都有一个 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_570 表,请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表,您的SQL查询应该返回:
+-------+
| Name |
+-------+
| John |
+-------+
*/
-- 解题
SELECT
Name
FROM
Employee_570 AS t1 JOIN
(SELECT
ManagerId
FROM
Employee_570
GROUP BY ManagerId
HAVING COUNT(ManagerId) >= 5) AS t2
ON t1.Id = t2.ManagerId;