SQL：如何循环 SELECT 语句的结果？

Question

如何循环遍历 SQL 中 SELECT 语句的结果？我的 SELECT 语句将仅返回 1 列，但返回 n 个结果。

我在下面创建了一个虚构的场景，其中包含我想要做的伪代码。

场景：

学生正在注册课程。他们提交了一份包含多个课程选择的表格（即一次选择 3 门不同的课程）。当他们提交注册时，我需要确保他们选择的课程中仍有剩余空间（请注意，在向他们提供课程选择用户界面之前，我会进行类似的检查，但之后我需要进行验证，以防其他人进入并刷卡）剩下的位置）。

伪代码：

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

Answer 1

不需要循环。您正在查看包含 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。

JOIN 现在

• 获取该学生的课程
• ，然后查看该课程的所有学生，并与

上述容量缩减版本进行比较：

...
-- 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

Answer 2

尽可能避免在 SQL 中循环结果集。如果你真的不能（如果你确实是一个标准程序员，但职业引导你进入 SQL），请使用 光标。它们闻起来不太好闻，但有时是不可避免的。

Answer 3

另一种选择是在包含课程信息。检查约束可以调用现有函数来检查是否有空闲座位。

将所有插入/更新包装到一笔事务中。如果任何插入/更新失败，则整个事务将回滚。