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

Question

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

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

设想:

学生正在注册他们的类(class)。他们提交包含多个类(class)选择的表格(即一次选择 3 门不同的类(class))。当他们提交注册时，我需要确保他们选择的类(class)仍有剩余空间(注意，在向他们展示类(class)选择 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

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。

现在加入

获取该学生的类(class)

然后查看该类(class)的所有学生并与容量进行比较

上面的删减版:

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