CourseName

CourseName

关注
发信
关注(28)粉丝(399)

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

本文介绍了SQL：如何循环SELECT语句的结果？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何循环访问SQL中的SELECT语句的结果？我的SELECT语句只返回1列，但返回n个结果。

我创建了一个虚构的场景，下面是我正在尝试做的伪代码。 p>

场景：

学生正在注册他们的课程。他们提交了多个课程选择的表格（即一次选择3个不同的课程）。当他们提交他们的注册时，我需要确保他们选择的课程还留有余地（注意我会在向他们提供课程选择用户界面之前进行类似的检查，但是如果有其他人进入并sw过假的代码：

  DECLARE @@ b 
 
 
 StudentId = 1 
 DECLARE @Capacity = 20 
 
  - 类将返回一个INTS列表的结果
 @Classes = SELECT classId FROM Student.CourseSelections 
 WHERE Student.CourseSelections = @StudentId 
 
 BEGIN TRANSACTION 
 DECLARE @ClassId int 
 foreach（@classId in @Classes）
 {
 （@SeatsTaken> @Capacity）
 {
 ROLLBACK; - 我会恢复他们所有的选择到这一点
 RETURN -1; 
} 
 else 
 {
  - 设置一些标志，以便学生确认班级
} 
} 
 
 COMMIT 
 RETURN 0

我真正的问题是类似的售票问题。所以如果这种方法看起来很不对劲，请随意推荐一些更实用的东西。

编辑：

$ b

试图执行下面的解决方案。此时它不起作用。总是返回保留。

pre $ 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'）

- 课程
DECLARE @Courses TABLE
（
CourseId int
，Capacity int
，CourseName nvarchar（max）
）

INSERT INTO @课程
（CourseId，Capacity，CourseName）
价值
（1，2，'英文文献'），
（2,10，'体育课程'），
（3，2，'Photography'）


- 链接表
DECLARE @Courses_Students表
（
Course_Student_Id int
， CourseId int
，StudentId int
）

INSERT INTO @Courses_Students
（Course_Student_Id，StudentId，CourseId）
VALUES
（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学生INNER JOIN
@Courses_Students Courses_Students ON Courses_Students.StudentId = Students.StudentId INNER JOIN
@Courses Courses ON Course.CourseId = Courses_Students.CourseId

DECLARE @StudentId int = 4

- 理想情况下，容量将由数据库驱动
- 即。来自课程。
- 但是我不想让HAVING声明复杂化，因为它似乎已经不起作用了。
DECLARE @Capacity int = 1

IF EXISTS（选择*
FROM
@Courses课程INNER JOIN
@Courses_Students课程_Students ON Courses_Students.CourseId =课程.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


解决方案

不需要循环。你正在寻找一个标准的聚合与COUNT和GROUP。

当然，一些细节是必要的，但原则是这样的... $ / b
$ b 

  DECLARE @StudentId = 1 
 DECLARE @Capacity = 20 
 
  - 类将是Select语句的结果它返回一个ints列表
 IF EXISTS（SELECT * 
 FROM 
 Student.CourseSelections CS 
 JOIN 
 ---这是你在哪里找出课程分配
 ClassTable C ON CS.classId = C.classId 
 WHERE 
 Student.CourseSelections = @StudentId 
 GROUP BY  - 改变这个，取决于你在哪里找到课程分配
 ClassID 
 HAVING 
 COUNT（*）> @Capacity）
'no'
 ELSE 
'yes'

编辑：

我已经更改了链接表。 Course_Student_ID通常不需要链接表。



现在加入


  • 那么该学生的课程

  • 然后查看所有学生的课程情况，并与能力进行比较





  ... 
  - 链接表
 DECLARE @Courses_Students表（
，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 
 
  - 直列表
 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.容量
 HAVING 
 COUNT（*）> C.Capacity


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

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

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
解决方案

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.

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

这篇关于SQL：如何循环SELECT语句的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-18 21:56
Powered by LMLPHP ©2024 CourseName 0.038387