如何使用COUNT（）和GROUP BY显示每个部门的员工人数

Question

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

Employee:
EmployeeID
Name
DeptID

Department:
DepartmentID
DeptName
LocID

Employee.DeptID is a foreign key to Department.DepartmentID

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

etc...

Answer 1

使用分组依据

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

左联接用于包括没有员工的部门。