如何在SQL中循环遍历SELECT语句的结果？我的SELECT语句将仅返回1列但n个结果。
下面是一个虚构的场景，包括我要做的事情的伪代码。
场景：
学生正在注册他们的课程。他们提交了一个表单，其中包含多个课程选择（即一次选择3个不同的课程）。当他们提交注册时，我需要确保所选的课程还有空位（请注意，在向他们呈现课程选择UI之前，我将进行类似的检查，但我需要在之后验证，以防其他人已经占用了剩余的位置）。
伪代码：
下面是一个虚构的场景，包括我要做的事情的伪代码。
场景：
学生正在注册他们的课程。他们提交了一个表单，其中包含多个课程选择（即一次选择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