本文介绍了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