我整天都在捣鼓按钮,但无法使这个查询起作用。我有 3 个表学生、课程和注册表,显示学生注册了哪些课程
该查询需要检索至少有 2 个学生注册的所有课程,这些课程按学生人数最多的课程排序
我想出了如何检索每个班级的注册人数,但无法将注册人数过滤为 >= 2 名学生
-- coursetable -----------------------------
CREATE TABLE StudentTable(
studentID VARCHAR(255) NOT NULL,
firstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
DOB DATE NULL,
CONSTRAINT pk_studentTable PRIMARY KEY(studentID)
);
-- coursetable -----------------------
CREATE TABLE CourseTable(
courseID VARCHAR(255) NOT NULL,
courseName VARCHAR(255) NOT NULL,
hoursPerWeek int(11) NULL,
startDate DATE NULL,
CONSTRAINT pk_courseTable PRIMARY KEY(courseID)
);
-- enrolment table --
CREATE TABLE EnrolmentTable(
studentID VARCHAR(255) NOT NULL,
CourseID VARCHAR(255) NOT NULL,
CONSTRAINT pk_enrolmentTable PRIMARY KEY(studentID, CourseID)
);
这是我可以执行的查询,显示所有班级的注册情况,但它显示一个班级只有 1 名学生注册。我需要它只显示=> 2 个注册的课程
SELECT ct.CourseName AS Course_Name, COUNT(st.studentID) AS Students_Enrolled
FROM EnrolmentTable et
INNER JOIN courseTable ct ON ct.courseID = et.courseID
INNER JOIN studentTable st ON st.studentID = et.studentID
GROUP BY et.courseID;
我需要使用子查询对吗?但不确定如何
您可以使用HAVING
过滤结果
SELECT ct.CourseName AS Course_Name, COUNT(st.studentID) AS Students_Enrolled
FROM EnrolmentTable et
INNER JOIN courseTable ct ON ct.courseID = et.courseID
INNER JOIN studentTable st ON st.studentID = et.studentID
GROUP BY et.courseID
HAVING Students_Enrolled> 1
ORDER BY Students_Enrolled DESC
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句