sql-server - 比较其他两个表的结果集时出现 SQL Server : Iterate over rows of one table,

Question

我有 3 个看起来像这样的表；

学生

studentID    studentName
1            John
2            Jack
3            Jane

主题

subjectID    subjectName
1            maths
2            geography
3            history
4            physics

性能

studentID    subject_passed    mark_obtained
1            maths             40
2            physics           50
1            geography         40 
3            maths             80
1            physics           40
2            maths             70
2            geography         40
1            history           30

使用上面的示例，我想要做的是选择所有通过所有科目且总分超过 120 分的学生。

这是我迄今为止尝试过的；

1. 使用 CURSOR(和 while 循环)迭代学生 ID，然后使用 EXCEPT 查找两个结果集之间的差异，如下所示

[declarations]
[set CURSOR]
[FETCH NEXT]
while @@FETCH_STATUS
BEGIN
    SELECT subject FROM Subjects
        EXCEPT
    SELECT subject FROM Performance WHERE studentId=@id 
    [FETCH NEXT]
END

我在使用这种方法时遇到了两个挑战； 它返回 n(学生人数)个不同的结果集 考虑到 WHERE 子句中不允许使用聚合函数，我也无法弄清楚如何合并有关 SUM 的第二个条件。

我还尝试将“所有科目”选择到一个列表中，并将“学生通过的所有科目”选择到另一个列表中，然后像这样比较它们；从学生中选择学生，其中 (select string_agg(所有科目)) = (select string_agg(学生传递的科目))但我仍然遇到了同样的挑战，未能将 SUM 的第二个条件纳入到整个组合中。

要求是选择通过所有科目且总分超过120分的学生。我感谢任何有关如何进行此操作的指导。

Answer 1

使用CROSS JOIN获取每个学生和每个科目的列表，然后LEFT JOIN到表Performance。然后你可以使用HAVING子句过滤掉所有科目都通过了的，总分超过120的。我用条件计数过滤掉那些没有全部通过的，这里我只计数Performance 表中 mark_obtained 具有值 NULL 的行(我认为只有在学生/科目没有行的情况下才会发生这种情况) )。

CREATE TABLE dbo.Student (StudentID int, StudentName varchar(5));
INSERT INTO dbo.Student (StudentID,
                         StudentName)
VALUES(1,'John'),
      (2,'Jack'),
      (3,'Jane');

CREATE TABLE dbo.Subject (SubjectID int, SubjectName varchar(10));
INSERT INTO dbo.Subject (SubjectID,
                         SubjectName)
VALUES(1,'maths'),
      (2,'geography'),
      (3,'history'),
      (4,'physics');

CREATE TABLE dbo.Performance (StudentID int, subject_passed varchar(10), mark_obtained int);
INSERT INTO dbo.Performance (StudentID,
                             subject_passed,
                             mark_obtained)
VALUES (1,'maths',40),
       (2,'physics',50),
       (1,'geography',40),
       (3,'maths',80),
       (1,'physics',40),
       (2,'maths',70),
       (2,'geography',40),
       (1,'history',30);

GO

SELECT St.StudentID,
       St.StudentName,
       SUM(P.mark_obtained) AS Marks_obtained
FROM dbo.Student St
     CROSS JOIN dbo.Subject Su
     LEFT JOIN dbo.Performance P ON St.StudentID = P.StudentID
                                AND Su.SubjectName = P.subject_passed --This should really be ID
GROUP BY St.StudentID,
         St.StudentName
HAVING COUNT(CASE WHEN P.mark_obtained IS NULL THEN 1 END) = 0
   AND SUM(P.mark_obtained) > 120

GO

DROP TABLE dbo.Performance;
DROP TABLE dbo.Subject;
DROP TABLE dbo.Student;

这是比使用CURSOR更好的方法。 SQL Server 的优点是基于集合的方法，而不是迭代方法，而 Cursor 属于后者。

db<>fiddle