我是新来的SQL查询,所以不知道太多
我有一个名为注册的表
该表具有以下结构
Student_ID int,
Course varchar(15),
Score int,
Semester varchar(15),
Discipline varchar(10),
Campus varchar(15),
Degree varchar(10),
Year int
它不包含任何主要键,它具有来自同一大学4个不同校园的学生的数据
我需要在特定学期学习更多5年课程的学生总数
我希望我已经明确地提出了一个问题,如果有人可以。
使用 GROUP BY
和 HAVING COUNT
来获取所有参加五个课程的学生:
SELECT student_id
FROM yourtable
WHERE Semester = ....
GROUP BY student_id
HAVING COUNT(DISTINCT Course) > 5
要获取学生人数,您可以计算查询返回的行数:
SELECT COUNT(*) AS total FROM
(
SELECT student_id
FROM yourtable
WHERE Semester = ....
GROUP BY student_id
HAVING COUNT(DISTINCT Course) > 5
)
让您的5个课程注册的学生可以这样做...
SELECT CAMPUS, STUDENT_ID
FROM REGISTRATION
WHERE SEMESTER = 'GIVEN SEMESTER'
GROUP BY CAMPUS, STUDENT_ID
HAVING COUNT(*) > 5;
要获得5个以上课程注册的学生人数...
SELECT COUNT(*)
FROM REGISTRATION
WHERE SEMESTER = 'GIVEN SEMESTER'
GROUP BY CAMPUS, STUDENT_ID
HAVING COUNT(*) > 5;
您需要按校园和学生ID进行分组,因为在不同的校园中重复学生ID。另外,如果我正确理解您,则主要键应为综合(校园,学生_id,学期,年)。
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
我假设表名是大学