选择给定的先决条件列表完全满足的课程 [英] Select courses that are completely satisfied by a given list of prerequisites

问题描述

我正在尝试编写一个SQL查询，该查询将返回一个人有资格完成的课程列表(给定其完成的学科的列表)(用作先决条件).

I'm trying to write an SQL query that will return a list of courses that a person is eligible for given a list of their completed subjects (to be used as prerequisites).

我的数据库如此布置.

   Prerequisite:
   +---------------+---------------+
   |      Id       |     Name      |         (Junction table)
   |---------------|---------------|         CoursePrerequisites:
   |      1        |   Maths       |         +---------------+---------------+
   |      2        |   English     |         |  Course_FK    | Prerequisite_FK
   |      3        |   Art         |         |---------------|---------------|
   |      4        |   Physics     |         |      1        |      1        |
   |      5        |   Psychology  |         |      1        |      2        |
   +-------------------------------+         |      2        |      3        |
                                             |      2        |      5        |
   Course:                                   |      5        |      4        |
   +---------------+---------------+         +---------------v---------------+
   |      Id       |     Name      |
   |---------------|---------------|
   |      1        |   Course1     |
   |      2        |   Course2     |
   |      3        |   Course3     |
   |      4        |   Course4     |
   |      5        |   Course5     |
   +---------------v---------------+

我一直在尝试一个看起来像这样的查询:

I've been experimenting with a query that looks like:

    SELECT  DISTINCT C.*
        FROM    Course C
    INNER JOIN JNCT_Course_Prerequisites cp
        ON C.Id = cp.Course_FK
    WHERE   cp.Prerequisite_FK IN (SELECT Prerequisites.Id FROM Prerequisites Where Name = 'Art' AND Name = etc etc)

但是，这将返回以Art为前提条件的任何课程，而不仅仅是给定前提条件列表完全满足 要求的课程.例如.即使该课程还需要心理学，它也会在仅以艺术为前提的情况下返回课程2.

However this returns any course where Art is a prerequisite, not just the courses that are completely satisfied by the given list of prerequisites. E.g. It will return course 2 given only Art as the student's prerequisite, even though the course also requires Psychology.

我对SQL还是很陌生，所以如果我的表布局有问题等，或者这是一个看似简单的问题，请原谅.我一直在尽力寻找，但是只能找到解决此问题的方法.

I'm very new to SQL, so please excuse me if there is a problem with my table layout, etc or if this is a seemingly simple question. I've been searching around as best I could, but can only find what appears to be solutions to the inverse of this problem.

似乎我需要做一些不同的设置:

It seems I need to do a difference of sets:

• 构建一组具有先决条件的课程(是否满足每个先决条件?)
• 构建一系列没有先决条件的课程
• 执行套集操作的差异:选择第一套中存在的所有内容，第二套中不存在的所有内容 看起来很简单，但是我现在对联结表以及如何处理许多先决条件感到困惑.

• Construct a set of courses that have a prerequisite (for each prerequisite?)
• Construct a set of courses that don't have a prerequisite
• Perform a difference of sets operation: select all present in first set and not present in second Seems straight forward, but I'm confused now with the junction table and how to handle this across many prerequisites.

我希望返回的行至少满足给定的先决条件列表，而不是包含至少一个先决条件的行.

I wish to return rows that are at least completely satisfied by the list of given prerequisites, not rows that contain at least one of the prerequisites.

例如，如果给出了艺术"，英语"，心理学"的前提条件，那么唯一应该返回的是Course2的行(满足前提条件).

For example, if the prerequisites 'Art', 'English', 'Psychology' are given, then the only row that should be returned is that for Course2 (Pre-requisites are more than satisfied).

谢谢

推荐答案

SELECT  * FROM Course C LEFT JOIN Course_Prerequisites cp ON C.Id = cp.Course_FK 
         WHERE Prerequisite_FK IN (SELECT Prerequisites.Id FROM Prerequisites Where Name = 'Art' OR Name = etc etc)
 NOT EXISTS   
   (SELECT  * FROM Course C LEFT JOIN Course_Prerequisites cp ON C.Id = cp.Course_FK 
         WHERE Prerequisite_FK NOT IN (SELECT Prerequisites.Id FROM Prerequisites Where Name = 'Art' OR Name = etc etc))

这篇关于选择给定的先决条件列表完全满足的课程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！