查找注册了某个课程的学生，该课程存在该学生未通过的先决条件

Question

有时我得到一个语法错误，有时我只是得到错误的结果。所以我有一个名为注册的表和一个名为必备条件的表，它们看起来像：

Enrollment(StudentNo, CourseNo, Term, Section, Mark)
    StudentNo and CourseNo are primary keys
Prerequistie(CourseNo, PrereqCourseNo)
     CourseNo and PrereqCourseNo are primary keys

所以现在我想找出注册了一门没有必修课的课程的学生，也就是说，该课程在必修课表中有必修课，而学生没有必修课。如果存在未注册的必修课或未通过必修课的必修课，则学生不具备高级课程的必修课。如果学生获得的课程分数低于50分，则该学生的课程不及格。

下面是我得到的信息：

SELECT distinct Enrollment.StudentNo, enrollment.CourseNo, prereqNeeded.prereq 
from Enrollment, (SELECT ENROLLMENT.CourseNo, PrereqCourseNo, StudentNo
            from enrollment, prerequisite 
                where ENROLLMENT.CourseNo = PREREQUISITE.CourseNo AND 
                NOT EXISTS 
                (SELECT StudentNo, MARK 
                FROM ENROLLMENT 
                WHERE enrollment.CourseNo = PrereqCourseNo 
                AND MARK >= 50)) prereqNeeded 
WHERE Enrollment.StudentNo = prereqNeeded.StudentNo 
and prereqNeeded.CourseNo = ENROLLMENT.CourseNo

Answer 1

首先，你的意思可能是

(StudentNo, CourseNo)是Enrollment的Primary Key，

(CourseNo, PrereqCourseNo)是Prerequistie的Primary Key

一个表只有一个主键，而不是很多。在这种情况下，主键不是单个字段，而是两个字段的组合。

其次，它是Prerequistie还是Prerequisite？

关于你的问题，我认为这是你需要的(更新，第一个版本是错误的)：

该查询可以改写为“查找所有注册了某门课程的学生，该课程的前提条件是该学生未通过考试(passed)”。

SELECT e.StudentNo, e.CourseNo
FROM Enrollment e
WHERE EXISTS
  ( SELECT *
    FROM Prerequisite p
    WHERE p.CourseNo = e.CourseNo
      AND NOT EXISTS
            ( SELECT *
              FROM Enrollment ep
              WHERE ep.CourseNo = p.PrereqCourseNo
                AND ep.StudentNo = e.StudentNo
                AND ep.Mark >= 50
            )
  )

Answer 2

SELECT CurrentPrereqs.StudentNo, CurrentPrereqs.CourseNo, PrereqCourseNo, Mark
FROM
  (SELECT StudentNo, Current.CourseNo, PrereqCourseNo
     FROM
       (SELECT * from Enrollment where Mark IS NULL) AS Current
     LEFT JOIN Prerequisite p ON Current.CourseNo=p.CourseNo
  ) AS CurrentPrereqs
LEFT JOIN Enrollment PreviousMarks
ON (CurrentPrereqs.StudentNo=PreviousMarks.StudentNo AND
    CurrentPrereqs.PrereqCourseNo=PreviousMarks.CourseNo)
WHERE PreviousMarks.Mark IS NULL OR PreviousMarks.Mark <50;

一些测试数据:课程101是课程202和2202的先决条件；课程202是课程303的先决条件，课程1101是课程2202的先决条件：

select * from Prerequisite;
+----------+----------------+
| CourseNo | PrereqCourseNo |
+----------+----------------+
|      202 |            101 |
|      303 |            202 |
|     2202 |            101 |
|     2202 |           1101 |
+----------+----------------+

两名学生，其中一名学生试图在没有参加1101的情况下参加2202的考试(但已经通过了101)；另一名学生在101考试不及格后试图参加202的考试：

select * from Enrollment order by StudentNo;
+-----------+----------+------+---------+------+
| StudentNo | CourseNo | Term | Section | Mark |
+-----------+----------+------+---------+------+
|         1 |      101 | F01  |       1 |   92 |
|         1 |      202 | S01  |       1 |   88 |
|         1 |      303 | F02  |       1 | NULL |
|         1 |     2202 | F02  |       1 | NULL |
|         2 |      101 | F01  |       2 |   48 |
|         2 |      202 | F02  |       2 | NULL |
+-----------+----------+------+---------+------+

学生注册的当前班级有一个空标记(目前)。

查询的结果是：

+-----------+----------+----------------+------+
| StudentNo | CourseNo | PrereqCourseNo | Mark |
+-----------+----------+----------------+------+
|         1 |     2202 |           1101 | NULL |
|         2 |      202 |            101 |   48 |
+-----------+----------+----------------+------+

Answer 3

第一件让我吃惊的事情是

 SELECT distinct Enrollment.StudentNo, enrollment.CourseNo, prereqNeeded.prereq 
from Enrollment, (SELECT ENROLLMENT.CourseNo, PrereqCourseNo, StudentNo
            from enrollment, prerequisite 
                where ENROLLMENT.CourseNo = PREREQUISITE.CourseNo AND 
                NOT EXISTS 
                (SELECT StudentNo, MARK 
                FROM ENROLLMENT 
                WHERE enrollment.CourseNo = PrereqCourseNo 
                AND MARK >= 50)) prereqNeeded 
WHERE Enrollment.StudentNo = prereqNeeded.StudentNo 
and prereqNeeded.CourseNo = ENROLLMENT.CourseNo

我认为您需要保留表名的大小写。

 SELECT distinct Enrollment.StudentNo, Enrollment.CourseNo, prereqNeeded.prereq 
from Enrollment, (SELECT Enrollment.CourseNo, PrereqCourseNo, StudentNo
            from Enrollment, Prerequisite 
                where Enrollment.CourseNo = Prerequisite.CourseNo AND 
                NOT EXISTS 
                (SELECT StudentNo, MARK 
                FROM Enrollment 
                WHERE Enrollment.CourseNo = PrereqCourseNo 
                AND MARK >= 50)) prereqNeeded 
WHERE Enrollment.StudentNo = prereqNeeded.StudentNo 
and prereqNeeded.CourseNo = Enrollment.CourseNo 

可能是错误的，但在我使用的DB上，它会抱怨这一点。