SELECT, GROUP BY and COUNT query

Question

下面有一个示例表，

+-------------+-------------+-------------+----------+------------+---------------------------------+----------+---------------+
| employee_id | first_name  | last_name   | email    | joined_date| title                           | salary   | supervisor_id |
+-------------+-------------+-------------+----------+------------+---------------------------------+----------+---------------+
|         100 | John        | King        | EM1      | 1984-06-17 | CEO                             | 14000.00 |          NULL |
|         101 | Leona       | Kochhar     | EM2      | 1993-09-21 | COO                             | 10000.00 |           100 |
|         102 | Lex         | De Haan     | EM3      | 1992-01-13 | CFO                             | 9000.00  |           100 |
|         103 | Alexander   | Hunold      | EM4      | 2001-04-03 | Gamer                           |  5000.00 |           102 |
|         104 | Dave        | William     | EM5      | 2002-05-21 | Gamer                           |  2000.00 |           103 |
|         105 | David       | Austin      | EM6      | 2002-06-25 | Gamer                           |  2800.00 |           103 |
|         106 | Valli       | Longwind    | EM7      | 2002-02-43 | Gamer                           |  2800.00 |           103 |

某些员工是此表中其他员工的主管。请注意，supervisor_id是employee_id。

我的任务是只使用SELECT语句来获取主管的employee_id、first_name、工资以及主管下的员工总数。

在我看来，我知道我需要使用某种分组和计数。首先，COUNT每个主管下的员工数量，其次是GROUP BY主管ID。我使用简单的COUNT和GROUP BY通过以下查询获得了输出：

SELECT employee_id, COUNT(supervisor_id)
FROM EMPLOYEE
GROUP BY supervisor_id;

这将输出supervisor_id和supervisor_id下的雇员数量。即：

+---------------+----------------------+
| supervisor_id | COUNT(supervisor_id) |
+---------------+----------------------+
|          NULL |                    0 |
|           100 |                    2 |
|           101 |                    0 |
|           102 |                    0 |
|           103 |                    3 |

*上表略有修改-这是一个样本输出

如上所述，supervisor_id是同一表中员工的employee_id。我的问题是，我无法使此表显示employee_id、first_name和salary以及COUNT列。最终结果必须显示employee_id(链接到supervisor_id(、first_name、salary和COUNT。

我试过这个

SELECT employee_id, first_name, salary, COUNT(supervisor_id)
FROM EMPLOYEE
GROUP BY employee_id, first_name, salary, supervisor_id;

但这只是返回原始表。

当我尝试这个时

SELECT employee_id, first_name, salary, COUNT(supervisor_id)
FROM EMPLOYEE
GROUP BY supervisor_id;

它返回错误"SELECT列表的表达式#1不在GROUP BY子句中，并且包含非聚合列"，根据联机信息，这就是我创建查询的方式(不能混合聚合列和非聚合列？(。

有人能指导我吗？谢谢

Answer 1

我想你只需要自己LEFT JOIN在这里：

https://www.db-fiddle.com/f/miLeekBXxoiVV1SxuTYc2c/0

SELECT s.employee_id, s.first_name, s.last_name, s.salary, COUNT(e.employee_id)
FROM employee s
LEFT JOIN employee e
ON e.supervisor_id = s.employee_id
GROUP BY s.employee_id, s.first_name, s.last_name, s.salary;

顺便说一句，您在评论中提到要使用HAVING COUNT() = 0。您最好不要将HAVING用于此目的，而应将此查询更改为INNER JOIN。它将采取同样但更有效的方式。https://www.db-fiddle.com/f/gM6q1UyagrZ4e1EHRvJ1NU/0

Answer 2

请尝试此

SELECT E1.supervisor_id,count(E2.supervisor_id) FROM `EMPLOYEE` AS E1
LEFT JOIN (select supervisor_id FROM EMPLOYEE GROUP BY 
supervisor_id) AS E2 ON E1.supervisor_id = E2.supervisor_id
GROUP BY E1.supervisor_id