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

Question

有时我会遇到语法错误，有时只是得到错误的结果。所以我有一个叫Enrollment的表和一个叫Prerequisition的表，它们看起来像：

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？

---

关于你的查询，我认为这是你需要的（更新，第一个版本是错误的）：

查询可以改写为"查找所有报名参加存在前提条件的课程的学生，该学生未通过"

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 

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