SQL：如何遍历SELECT语句的结果？

去游山玩水吧

如何在SQL中遍历SELECT语句的结果？我的SELECT语句将仅返回1列，但返回n个结果。
我在下面创建了一个虚构的场景，其中包含我想做的事情的伪代码。
设想：
学生正在注册他们的课程。他们提交具有多个课程选择的表格（即一次选择3个不同的课程）。当他们提交注册时，我需要确保他们选择的课程还有剩余的空间（请注意，在向他们展示课程选择UI之前，我将进行类似的检查，但是之后我需要进行验证，以防其他人进入并刷卡剩下的位置）。
伪代码：
DECLARE @StudentId = 1
DECLARE @Capacity = 20
-- Classes will be the result of a Select statement which returns a list of ints
@Classes = SELECT classId FROM Student.CourseSelections
WHERE Student.CourseSelections = @StudentId
BEGIN TRANSACTION
DECLARE @ClassId int
foreach (@classId in @Classes)
{
SET @SeatsTaken = fnSeatsTaken @classId
if (@SeatsTaken > @Capacity)
{
   ROLLBACK;  -- I'll revert all their selections up to this point
   RETURN -1;
}
else
{
   -- set some flag so that this student is confirmed for the class
}
}
COMMIT
RETURN 0
我真正的问题是类似的“票务”问题。因此，如果此方法看起来非常错误，请随时推荐一些更实用的方法。
编辑：
尝试实施以下解决方案。在这一点上，它是行不通的。始终返回“保留”。
DECLARE @Students TABLE
(
StudentId int
,StudentName nvarchar(max)
)
INSERT INTO @Students
(StudentId ,StudentName)
VALUES
(1, 'John Smith')
,(2, 'Jane Doe')
,(3, 'Jack Johnson')
,(4, 'Billy Preston')
-- Courses
DECLARE @Courses TABLE
(
CourseId int
,Capacity int
,CourseName nvarchar(max)
)
INSERT INTO @Courses
(CourseId, Capacity, CourseName)
VALUES
(1, 2, 'English Literature'),
(2, 10, 'Physical Education'),
(3, 2, 'Photography')

-- Linking Table
DECLARE @Courses_Students TABLE
(
Course_Student_Id int
,CourseId int
,StudentId int
)
INSERT INTO @Courses_Students
(Course_Student_Id, StudentId, CourseId)
VALUES
(1, 1, 1),
(2, 1, 3),
(3, 2, 1),
(4, 2, 2),
(5, 3, 2),
(6, 4, 1),
(7, 4, 2)
SELECT Students.StudentName, Courses.CourseName FROM @Students Students INNER JOIN
@Courses_Students Courses_Students ON Courses_Students.StudentId = Students.StudentId INNER JOIN
@Courses Courses ON Courses.CourseId = Courses_Students.CourseId
DECLARE @StudentId int = 4
-- Ideally the Capacity would be database driven
-- ie. come from the Courses.Capcity.
-- But I didn't want to complicate the HAVING statement since it doesn't seem to work already.
DECLARE @Capacity int = 1
IF EXISTS (Select *
FROM
  @Courses Courses INNER JOIN
  @Courses_Students Courses_Students ON Courses_Students.CourseId = Courses.CourseId
WHERE
  Courses_Students.StudentId = @StudentId
GROUP BY
  Courses.CourseId
HAVING
  COUNT(*) > @Capacity)
BEGIN
SELECT 'full' as Status
END
ELSE BEGIN
SELECT 'reserved' as Status
END

解决方案:


            无需循环。您正在查看具有COUNT和GROUP的标准聚合。
当然，需要一些细节，但是原理是…
DECLARE @StudentId = 1
DECLARE @Capacity = 20
-- Classes will be the result of a Select statement which returns a list of ints
IF 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 Table
DECLARE @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 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
--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

SQL：如何遍历SELECT语句的结果？

去游山玩水吧 LV1