MySQL-11练习:至少有五名直接下属的经理
题目
表Employee
包含所有员工及其上级的信息。每位员工都有一个Id,并且还有一个对应主管的Id(ManagerId)。
+------+----------+-----------+----------+
|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个下属的主管。对于上面的表,结果应输出:
+-------+
| Name |
+-------+
| John |
+-------+
解答
SELECT Name
FROM Employee AS k1
JOIN (SELECT ManagerId
FROM Employee
GROUP BY ManagerId
HAVING COUNT(ManagerId) >= 5) AS k2
ON k1.Id = k2.ManagerId;