sql - 如何统计每个老师教过多少学生？

Question

我有一个包含下表的数据库

Courses: CourseID*, CourseName, TeacherID
Teachers: TeacherID*, TeacherName
Students: StudentID*, StudentName
StudentCourses: CourseID*, StudentID*

图例: * 是主键。

我如何编写查询以产生每位教师所教学生的数量？

例如

TeacherName, Count
Bob 15
Sarah 5
Zubair 1

编辑

select "TeacherName", count(*)
from courses inner join teachers on courses."TeacherID" = teachers."TeacherI"
join sutdentcourses on sutdentcourses."CourseID" = courses."CourseID"
group by "TeacherName"
order by "TeacherName";

Answer 1

您需要在表 Teachers、Courses 和 StudentCourses 之间进行 JOIN，按 TeacherName 对记录进行分组并计算 StudentID。

SELECT TeacherName, COUNT(StudentID)
FROM Teachers JOIN Courses USING(TeacherID)
JOIN StudentCourses USING(CourseID)
GROUP BY TeacherName