如何使用count(*)从另一个具有条件和分组by的表中进行选择?

Question

我正在处理Students和departments两个表

StudentId    |     Name       |    Address    |  DepartmentId
1        |    Simons      |    Los Angeles|   001
2        |    Mark        |    Texas      |   001
3        |    Kelly       |    Florida    |   002
4        |    Philip      |    New York   |   002

<<p>部门/strong>

DepartmentId   |     Name       |    Date_Creation
001        |    Maths       |    01/09/2021
002        |    Physics     |    01/08/2021 

我真正需要的是从Department Table中检索所有信息，其中包含来自Department (group by)的学生人数。

预期结果:

DepartmentId   |     Name        |  NumberOfStudents
001        |    Maths        |        2 
002        |    Physics      |        2

我已经尝试执行查询，但它没有给我任何结果:

Select DepartmentId, Name, (select count(*)
from Students s, Departments d
where s.DepartmentId = d.DepartmentId) as NumberOfStudents 

我可以修改什么来得到需要的结果?

Answer 1

您也可以使用APPLY或预分组连接

SELECT
d.Name,
s.cnt
FROM dept AS d
OUTER APPLY (
SELECT COUNT(*) AS cnt
FROM students AS s
WHERE s.DepartmentId = d.DepartmentId
) s;
-- alternatively
SELECT
d.Name,
ISNULL(s.cnt, 0) cnt
FROM dept AS d
LEFT JOIN (
SELECT s.DepartmentId, COUNT(*) AS cnt
FROM students AS s
GROUP BY s.DepartmentId
) s ON s.DepartmentId = d.DepartmentId;

Answer 2

也许?

SELECT
d.Name,
COUNT(s.StudentId) AS cnt
FROM students AS s
INNER JOIN dept AS d
ON s.DepartmentId = d.DepartmentId
GROUP BY d.Name