SQL 如何在sql中获得每门课程的学生人数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25681018/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get number of students per course in sql?
提问by Hristow
So I have these 3 tables:
所以我有这 3 个表：
t_student which looks like this:
t_student 看起来像这样：
STUDENT_ID| FIRST_NAME |LAST_NAME
-----------------------------------
1 | Ivan | Petrov
2 | Ivan | Ivanov
3 | Georgi | Georgiev
t_course which looks like this:
t_course 看起来像这样：
course_id | NAME |LECTURER_NAME
-----------------------------------
1 | Basics | Vasilev
2 | Photography| Loyns
t_enrolment which looks like this:
t_enrolment 看起来像这样：
enrolment_id| student_fk |course_fk | Avarage_grade
-------------------------------------------------------
1 | 1 | 1 |
2 | 3 | 1 |
3 | 4 | 1 |
4 | 2 | 1 |
5 | 1 | 2 | 5.50
6 | 2 | 2 | 5.40
7 | 5 | 2 | 6.00
I need to make 'select' statement and present the number of students per course. The result should be:
我需要做出“选择”声明并提供每门课程的学生人数。结果应该是：
Count_students | Course_name
-----------------------------
4 | Basics
3 | Photography
回答by TAK
Select all courses from your course Table, join the enrolment table and group by your course id. With count() you can select the number of Students
从您的课程表中选择所有课程，加入注册表并按您的课程 ID 分组。使用 count() 您可以选择学生人数
SELECT MAX(t_course.NAME) AS Course_name, COUNT(t_enrolment.student_fk) AS Count_students
FROM t_course
LEFT JOIN t_enrolment ON t_enrolment.course_fk = t_course.course_id
GROUP BY t_course.course_id;
If you want to select the same student in one course only once (if more then one enrolment can happen) you can use COUNT(DISTINCT t_enrolment.student_fk)
如果您只想在一门课程中选择同一个学生一次（如果可以发生多于一个注册），您可以使用 COUNT(DISTINCT t_enrolment.student_fk)
UPDATE
更新
To make it working not only in mySQL I added an aggregate function to the name column.
为了使它不仅在 mySQL 中工作，我在 name 列中添加了一个聚合函数。
Depending on the SQL database you are using you will have to add quotes or backticks.
根据您使用的 SQL 数据库，您必须添加引号或反引号。
回答by Denis Kohl
Is this your homework?
这是你的家庭作业吗？
select count(*) Count_students, c.name as course_name from t_enrolment e, t_course c group where e.course_fk = c.course_id by c.name
回答by Edi G.
You need a select statement with a join to the couse table (for the Course_name). Group by 't_course'.nameto use the COUNT(*)function
您需要一个连接到 couse 表的 select 语句（对于Course_name）。分组't_course'.name以使用该COUNT(*)功能
this will work:
这将起作用：
SELECT COUNT(*) AS Count_students, c.NAME AS Course_name
FROM t_enrolment e
JOIN course c
ON e.course_fk = c.course_id
GROUP BY c.NAME
More information
更多信息
