mysql - 如何使用COUNT()和GROUP BY显示每个部门的员 worker 数

Question

请考虑两个表 - 员工和部门

Employee:
EmployeeID
Name
DeptID

Department:
DepartmentID
DeptName
LocID

Employee.DeptID is a foreign key to Department.DepartmentID

如何显示所有部门(部门名称)的列表以及每个部门的员 worker 数？输出应如下所示:

DepartmentName         Number of employees
Accounts               30
HR                     24
Production             400
Sales/Marketing        250

etc...

Answer 1

使用GROUP BY

 SELECT d.deptID, count(e.deptID)
 FROM Department d
 LEFT JOIN Employee e ON d.DeptID = e.DeptID
 GROUP BY d.deptId

和LEFT JOIN用于包含没有员工的部门。