570. Managers with at Least 5 Direct Reports

id is the primary key (column with unique values) for this table.
Each row of this table indicates the name of an employee, their department, and the id of their manager.
If managerId is null, then the employee does not have a manager.
No employee will be the manager of themself.

Write a solution to find managers with at least five direct reports.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:
Employee table:

Output:

解答：

SELECT emplA.name 
FROM Employee emplA
INNER JOIN Employee emplB  ON emplA.id = emplB.managerId
GROUP BY emplB.managerId
HAVING COUNT(emplB.managerId) >= 5

HAVING 子句是用來取代 WHERE 搭配聚合函數 (aggregate function) 進行條件查詢，因為 WHERE 不能與聚合函數一起使用。

第一次寫的sql

SELECT e.name 
FROM Employee e
WHERE COUNT(e.managerId = e.id) >= 5

明顯犯下 WHERE 不能與聚合函數一起使用。

解題思路：
先下這段sql會發現emplA.managerId 是NULL ,所以GROUP BY時用emplB.managerId
然後條件也要用HAVING COUNT(emplB.managerId) >= 5

SELECT *
FROM Employee emplA
INNER JOIN Employee emplB  ON emplA.id = emplB.managerId