MySQL-11练习：至少有五名直接下属的经理

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;