SQL:如何循环遍历SELECT语句的结果?

How do I loop through the results of a SELECT statement in SQL? My SELECT statement will return just 1 column but n results.

如何在SQL中循环SELECT语句的结果?SELECT语句只返回1列，但返回n个结果。

I have created a fictional scenario below complete with the Pseudo code of what I'm trying to do.

我在下面创建了一个虚构的场景，并用伪代码完成了我要做的事情。

Scenario:

场景:

Students are registering for their classes. They submit a form with multiple course selections (ie. select 3 different courses at once). When they submit their registration I need to ensure there is still room left int the courses they have selected (note I will do a similar check before presenting them with course selection UI but I need to verify afterwards in case somebody else has gone in and swipped up the remaining spots).

学生们正在注册他们的课程。他们提交一份包含多种课程选择的表格。同时选择3门不同的课程)。当他们提交注册,我需要确保还有房间int他们选择的课程(注意我将做一个类似的检查之前他们选课界面但我需要验证之后,以防别人已经在swip剩余位置)。

Pseudo Code:

伪代码:

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

My real problem is a similar "ticketing" problem. So if this approach seems very wrong please feel free to recommend something more practical.

我真正的问题是类似的“票务”问题。因此，如果这种方法看起来非常错误，请随意推荐一些更实用的方法。

EDIT:

编辑:

Attempting to implement the solution below. At this point it doesn't work. Always returns "reserved".

尝试实现下面的解决方案。在这一点上行不通。总是返回“保留”。

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

3 个解决方案

#1

No loop needed. You're looking at a standard aggregate with COUNT and GROUP.

不需要循环。你看到的是一个带计数和组的标准集合。

Of course, some details are needed but the principle is this...

当然，需要一些细节，但原则是……

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'

Edit:

编辑:

I've changed the link table. Course_Student_ID is usually not needed in link tables.

我改变了链接表。在链接表中，通常不需要使用Course_Student_ID。

The JOIN now

现在加入

• gets the courses for that student
• 获取那个学生的课程
• then looks at all students on this course and compares to capacity
• 然后查看这门课上的所有学生，并与他们的能力进行比较

Cut down version of above:

削减以上版本:

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

#2

Avoid looping through result sets in SQL as much as you can. If you really can't (if you really are a standard programmer but profession leads you into SQL) use cursors. They don't smell nice, but are unavoidable at times.

尽量避免在SQL中遍历结果集。如果您真的不能使用游标(如果您确实是一个标准的程序员，但是专业需要您使用SQL)，那么请使用游标。它们闻起来不香，但有时是不可避免的。

#3

Another option would be to implement a CHECK Constraint on your table that contains the Course information. The check constraint could call your existing function to check that there are free seats.

另一个选项是在包含课程信息的表上实现检查约束。检查约束可以调用您的现有函数来检查是否有空闲的位置。

Wrap all of your Inserts/Updates in to one transaction. If any of the Inserts/Updates fails then the entire transaction will be rolled back.

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