SQL 查询，用于获取在一个学期内注册超过 5 门课程的学生总数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14034412/
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
SQL Query for getting total students who registered for more then 5 courses in a semester
提问by user1676346
I am new with sql queries so dont know much
我是 sql 查询的新手，所以不太了解
i have a table named registration
我有一个名为注册的表
this table has the following structure
该表具有以下结构
Student_ID int,
Course varchar(15),
Score int,
Semester varchar(15),
Discipline varchar(10),
Campus varchar(15),
Degree varchar(10),
Year int
it does not contain any primary key it has the data of students from 4 different campuses of the same university so student_id is repeated
它不包含任何主键它有来自同一所大学的 4 个不同校区的学生的数据，所以 student_id 是重复的
i am required total number of students who have taken more then 5 courses in a particular semester
我需要在特定学期选修超过 5 门课程的学生总数
i hope i have made the question clear kindly help if any one can.
我希望我已经清楚地说明了这个问题，如果有人可以的话，请提供帮助。
回答by Mark Byers
Use GROUP BYand HAVING COUNTto get all the students that have taken more than five courses:
使用GROUP BY和HAVING COUNT获取所有参加过五门以上课程的学生：
SELECT student_id
FROM yourtable
WHERE Semester = ....
GROUP BY student_id
HAVING COUNT(DISTINCT Course) > 5
To get the number of students you can count the number of rows that query returns:
要获取学生人数，您可以计算查询返回的行数：
SELECT COUNT(*) AS total FROM
(
SELECT student_id
FROM yourtable
WHERE Semester = ....
GROUP BY student_id
HAVING COUNT(DISTINCT Course) > 5
)
回答by Waleed
To get the students who are registered for more than 5 courses you do this...
要让注册超过 5 门课程的学生，您可以这样做...
SELECT CAMPUS, STUDENT_ID
FROM REGISTRATION
WHERE SEMESTER = 'GIVEN SEMESTER'
GROUP BY CAMPUS, STUDENT_ID
HAVING COUNT(*) > 5;
To get the number of students who are registered for more than 5 courses you do this...
要获得注册超过 5 门课程的学生人数，您可以这样做...
SELECT COUNT(*)
FROM REGISTRATION
WHERE SEMESTER = 'GIVEN SEMESTER'
GROUP BY CAMPUS, STUDENT_ID
HAVING COUNT(*) > 5;
You need to group by campus AND student id since student id is repeated for different campuses. Also, the primary key should be composite (campus, student_id, semester, year, course) if I understand you correctly.
您需要按校园和学生 ID 分组，因为不同校园的学生 ID 是重复的。此外，如果我理解正确的话，主键应该是复合的（校园、学生 ID、学期、年份、课程）。
回答by macio.Jun
SELECT COUNT(DISTINCT(sub.Student_ID)) FROM
(
SELECT un.Student_ID, COUNT(*) FROM university un
GROUP BY un.Student_ID, un.Semester
HAVING
COUNT(un.Course)>5
) AS sub
I assume the table name is university
我假设表名是大学
