SQL：如何遍历 SELECT 语句的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3901601/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: How do I loop through the results of a SELECT statement?
提问by Justin
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 门不同的课程）。当他们提交注册时，我需要确保他们选择的课程仍有剩余空间（注意，在向他们展示课程选择 UI 之前，我会做类似的检查，但我需要事后验证，以防其他人进入并刷卡剩下的地方）。
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, 'Hyman 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
回答by gbn
No loop needed. You're looking at a standard aggregate with COUNT and GROUP.
不需要循环。您正在查看带有 COUNT 和 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
回答by Daniel Mo?mondor
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）使用cursors。它们闻起来并不好闻，但有时是不可避免的。
回答by codingbadger
Another option would be to implement a CHECK Constrainton your table that contains the Course information. The check constraint could call your existing function to check that there are free seats.
另一种选择是在包含课程信息的表上实施CHECK 约束。检查约束可以调用您现有的函数来检查是否有空闲座位。
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.
将您的所有插入/更新包装到一个事务中。如果任何插入/更新失败，则整个事务将被回滚。
