无需循环。您正在查看具有COUNT和GROUP的标准聚合。
当然，需要一些细节，但是原理是…
DECLARE @StudentId = 1DECLARE @Capacity = 20-- Classes will be the result of a Select statement which returns a list of intsIF EXISTS (SELECT * FROM Student.CourseSelections CS JOIN ---this is where you find out course allocations somehow ClassTable C ON CS.classId = C.classId WHERe Student.CourseSelections = @StudentId GROUP BY --change this, it depends on where you find out course allocations ClassID HAVINg COUNT(*) > @Capacity) 'no'ELSE 'yes'
编辑：
我已经更改了链接表。链接表中通常不需要Course_Student_ID。
现在加入
- 得到那个学生的课程
- 然后查看本课程中的所有学生并与能力进行比较
缩减以上版本：
...-- linking TableDECLARE @Courses_Students TABLE (,CourseId int,StudentId int)INSERT INTO @Courses_Students (StudentId, CourseId)VALUES (1, 1), (1, 3), (2, 1), (2, 2), (3, 2), (4, 1), (4, 2)DECLARE @StudentId int = 4--straight listSELECT C.CourseName, C.Capacity, COUNT(*) FROM @Courses_Students CSThis JOIN @Courses C ON CSThis.CourseId = C.CourseId JOIN @Courses_Students CSOthers ON CSOthers.CourseId = C.CourseId WHERe CSThis.StudentId = @StudentId GROUP BY C.CourseName, C.Capacity--oversubscribed list SELECt C.CourseName, C.Capacity, COUNT(*) FROM @Courses_Students CSThis JOIN @Courses C ON CSThis.CourseId = C.CourseId JOIN @Courses_Students CSOthers ON CSOthers.CourseId = C.CourseId WHERe CSThis.StudentId = @StudentId GROUP BY C.CourseName, C.Capacity HAVINg COUNT(*) > C.Capacity
