PROSAGA码农传奇-复合模型-复合课程先决条件（a，b，c和x或y以及z样式中的一个或多个）

0# 威斯特 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”>
  
    我的模型与Kuba和Dmitry建议的略有不同，尽管两者都提供了写这个答案的一般框架。
  
  
    我提前道歉，我打算打破你现有模特的语言，因为我不觉得“艺术”和“心理学”是你的先决条件。它们只是组合在一起形成先决条件实体的课程。所以我已经重命名了这张桌子
    的
      主题
    </强>
    。
  
  
    所有数据模型都可以描述为可以在没有实际物理数据库的情况下描述的实体和关系。在这种情况下，你有一个具有挑战性的实体，即
    的
      条件
    </强>
    。作为一个实体，它由其中的主题和其课程所需的主题数量来表示。这很适合课程目录，你可以对一门课程说，每个先决条件的每个先决条件是什么（“艺术，心理学 - 1需要”，“艺术和心理学 - 所有必需”等等。 ）
  
  
    第一个问题是：
  
  <UL>
    <LI>
      是给定先决条件所需的科目数
每个课程都有足够的独特性和特殊性，或“艺术或艺术”
心理学 - 1要求“适用于大量课程？每门课程经常改变，还是相对静止？
    </LI>
  </UL>
  
    如果它适用于大量课程或相对静态，则应该位于先决条件表中。如果课程的每组先决条件都是相对动态的，那么它应该位于PrerequisiteCourse表中。现在我将假设前者。
  
  
    实际主题是多对多关系（每个主题可以是许多先决条件的一部分，每个先决条件可以有许多主题），并且应该在其中的交叉引用表中建模。
  
  
    从这里可以明显看出，“艺术与心理学 - 选择1”和“艺术与心理学 - 两者都需要”是独特的实体。因此，我将清楚地确定每组可能的先决条件
    
      包括所需科目的数量
    
    。
  
   <pre>
 <code>
 Prerequisite
----------------------
PrerequisiteID
NumberOfSubjectsRequired

Subject
---------------
SubjectID
Name

PrerequisiteSubject
--------------------
PrequisiteSubjectID
PrerequisiteID
SubjectID

Course
------
CourseID

CoursePrerequisite
------------------
CoursePrerequisiteID
PrerequisiteID
CourseID

</code>
 </pre>
  
    请注意，通过确保不同的必备主题组合列表，Dmitry的模型如何改进（如果我可能如此大胆）
    
      和
    
     允许在所有课程中重复使用诸如“艺术或心理学 - 选择1”等先决条件。这是（在我看来，基于我对您的数据模型的理解）对先决条件的正确建模。考虑“艺术和心理学 - 选择1”被修改为包括所有课程的演讲的场景。在这里，您可以在一个地方插入一行（PrerequisiteSubject表），它将适用于其中的所有课程，而不会打扰任何其他先决条件。
  
  
    另一个优点是在查询中：对于给定的一组主题，获得给定学生会遇到的先决条件（假设SubjectsTaken是学生已经学习的科目）：
  
   <pre>
 <code>
 select 
 case when count(1) >= ct then 1 else 0 end as PrerequisiteMet,
 p.PrerequisiteID
 from
 subjectstaken st
 left join [subject] s
 inner join Prerequisitesubject PS
 inner join Prerequisite P
 on PS.prerequisiteid = P.prerequisiteiD
 on S.subjectid = PS.subjectID
 on s.name = st.name
 group by
 p.PrerequisiteID, ct

</code>
 </pre>
  
    然后是学生可以参加的课程：
  
   <pre>
 <code>
 select 
courseid
from prerequisitesmet pm
right join 
prerequisitecourse pc
on pc.prerequisiteid = pm.PrerequisiteID
group by
courseid
having sum(prerequisitemet) >= count(1)

</code>
 </pre>
  
    无论如何，所有这些建模都取决于你的“可重用”实体。感觉先决条件应该是一个可重用的实体，但我可能是错的。
  
</DIV>

1# 纾潆锦袖迷子 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”>
  
    这是我的一个培训实验室对于高级SQL的剪切和粘贴，我希望它是正确的，我现在无法测试它，听起来与你的任务相似。
  
  
    它只是使用比萨饼和浇头，我通常在午餐前做:-)
  
   <pre>
 <code>
 CREATE TABLE Pizzas
(Pizza# INTEGER NOT NULL PRIMARY KEY,
 PizzaName VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO Pizzas VALUES(1, 'Margherita')
;INSERT INTO Pizzas VALUES(2, 'Salami')
;INSERT INTO Pizzas VALUES(3, 'Prosciutto')
;INSERT INTO Pizzas VALUES(4, 'Funghi')
;INSERT INTO Pizzas VALUES(5, 'Hawaii')
;INSERT INTO Pizzas VALUES(6, 'Calzone')
;INSERT INTO Pizzas VALUES(7, 'Quattro Stagioni')
;INSERT INTO Pizzas VALUES(8, 'Marinara')
;INSERT INTO Pizzas VALUES(9, 'Vegetaria')
;INSERT INTO Pizzas VALUES(10, 'Diavola')
;INSERT INTO Pizzas VALUES(11, 'Tonno')
;INSERT INTO Pizzas VALUES(12, 'Primavera')
;INSERT INTO Pizzas VALUES(13, 'Gorgonzola')
;INSERT INTO Pizzas VALUES(14, 'Fantasia')
;INSERT INTO Pizzas VALUES(15, 'Quattro Formaggi')
;INSERT INTO Pizzas VALUES(16, 'Napolitane')
;INSERT INTO Pizzas VALUES(17, 'Duplicato')
;

CREATE TABLE Toppings
(Topping# INTEGER NOT NULL PRIMARY KEY,
 Topping VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO Toppings VALUES(1, 'Tomatoes')
;INSERT INTO Toppings VALUES(2, 'Mozzarella')
;INSERT INTO Toppings VALUES(3, 'Salami')
;INSERT INTO Toppings VALUES(4, 'Mushrooms')
;INSERT INTO Toppings VALUES(5, 'Chillies')
;INSERT INTO Toppings VALUES(6, 'Pepper')
;INSERT INTO Toppings VALUES(7, 'Onions')
;INSERT INTO Toppings VALUES(8, 'Garlic')
;INSERT INTO Toppings VALUES(9, 'Olives')
;INSERT INTO Toppings VALUES(10, 'Capers')
;INSERT INTO Toppings VALUES(11, 'Tuna')
;INSERT INTO Toppings VALUES(12, 'Squid')
;INSERT INTO Toppings VALUES(13, 'Pineapple')
;INSERT INTO Toppings VALUES(14, 'Spinach')
;INSERT INTO Toppings VALUES(15, 'Scallop')
;INSERT INTO Toppings VALUES(16, 'Ham')
;INSERT INTO Toppings VALUES(17, 'Gorgonzola')
;INSERT INTO Toppings VALUES(18, 'Asparagus')
;INSERT INTO Toppings VALUES(19, 'Fried egg')
;INSERT INTO Toppings VALUES(20, 'Anchovies')
;INSERT INTO Toppings VALUES(21, 'Corn')
;INSERT INTO Toppings VALUES(22, 'Artichock')
;INSERT INTO Toppings VALUES(23, 'Seafood')
;INSERT INTO Toppings VALUES(24, 'Brokkoli')
;INSERT INTO Toppings VALUES(25, 'Anchovis')
;INSERT INTO Toppings VALUES(26, 'Parmesan')
;INSERT INTO Toppings VALUES(27, 'Goat cheese')
;

CREATE TABLE PizzaToppings
(Pizza# INTEGER NOT NULL,
 Topping# INTEGER NOT NULL,
 UNIQUE (Pizza#, Topping#)
) PRIMARY INDEX(Pizza#);

INSERT INTO PizzaToppings VALUES(1, 1)
;INSERT INTO PizzaToppings VALUES(1, 2)
;INSERT INTO PizzaToppings VALUES(2, 1)
;INSERT INTO PizzaToppings VALUES(2, 2)
;INSERT INTO PizzaToppings VALUES(2, 3)
;INSERT INTO PizzaToppings VALUES(3, 1)
;INSERT INTO PizzaToppings VALUES(3, 2)
;INSERT INTO PizzaToppings VALUES(3, 16)
;INSERT INTO PizzaToppings VALUES(4, 1)
;INSERT INTO PizzaToppings VALUES(4, 2)
;INSERT INTO PizzaToppings VALUES(4, 4)
;INSERT INTO PizzaToppings VALUES(5, 1)
;INSERT INTO PizzaToppings VALUES(5, 2)
;INSERT INTO PizzaToppings VALUES(5, 13)
;INSERT INTO PizzaToppings VALUES(5, 16)
;INSERT INTO PizzaToppings VALUES(6, 1)
;INSERT INTO PizzaToppings VALUES(6, 2)
;INSERT INTO PizzaToppings VALUES(6, 4)
;INSERT INTO PizzaToppings VALUES(6, 11)
;INSERT INTO PizzaToppings VALUES(6, 22)
;INSERT INTO PizzaToppings VALUES(7, 1)
;INSERT INTO PizzaToppings VALUES(7, 2)
;INSERT INTO PizzaToppings VALUES(7, 4)
;INSERT INTO PizzaToppings VALUES(7, 6)
;INSERT INTO PizzaToppings VALUES(7, 16)
;INSERT INTO PizzaToppings VALUES(8, 1)
;INSERT INTO PizzaToppings VALUES(8, 2)
;INSERT INTO PizzaToppings VALUES(8, 8)
;INSERT INTO PizzaToppings VALUES(8, 9)
;INSERT INTO PizzaToppings VALUES(8, 12)
;INSERT INTO PizzaToppings VALUES(8, 15)
;INSERT INTO PizzaToppings VALUES(8, 16)
;INSERT INTO PizzaToppings VALUES(8, 23)
;INSERT INTO PizzaToppings VALUES(9, 1)
;INSERT INTO PizzaToppings VALUES(9, 2)
;INSERT INTO PizzaToppings VALUES(9, 5)
;INSERT INTO PizzaToppings VALUES(9, 6)
;INSERT INTO PizzaToppings VALUES(9, 7)
;INSERT INTO PizzaToppings VALUES(9, 8)
;INSERT INTO PizzaToppings VALUES(9, 9)
;INSERT INTO PizzaToppings VALUES(9, 14)
;INSERT INTO PizzaToppings VALUES(9, 18)
;INSERT INTO PizzaToppings VALUES(10, 1)
;INSERT INTO PizzaToppings VALUES(10, 2)
;INSERT INTO PizzaToppings VALUES(10, 5)
;INSERT INTO PizzaToppings VALUES(10, 7)
;INSERT INTO PizzaToppings VALUES(10, 9)
;INSERT INTO PizzaToppings VALUES(10, 10)
;INSERT INTO PizzaToppings VALUES(11, 1)
;INSERT INTO PizzaToppings VALUES(11, 2)
;INSERT INTO PizzaToppings VALUES(11, 7)
;INSERT INTO PizzaToppings VALUES(11, 11)
;INSERT INTO PizzaToppings VALUES(12, 1)
;INSERT INTO PizzaToppings VALUES(12, 2)
;INSERT INTO PizzaToppings VALUES(12, 3)
;INSERT INTO PizzaToppings VALUES(12, 4)
;INSERT INTO PizzaToppings VALUES(13, 1)
;INSERT INTO PizzaToppings VALUES(13, 2)
;INSERT INTO PizzaToppings VALUES(13, 16)
;INSERT INTO PizzaToppings VALUES(13, 17)
;INSERT INTO PizzaToppings VALUES(13, 24)
;INSERT INTO PizzaToppings VALUES(14, 1)
;INSERT INTO PizzaToppings VALUES(14, 2)
;INSERT INTO PizzaToppings VALUES(14, 10)
;INSERT INTO PizzaToppings VALUES(14, 19)
;INSERT INTO PizzaToppings VALUES(14, 20)
;INSERT INTO PizzaToppings VALUES(14, 21)
;INSERT INTO PizzaToppings VALUES(15, 1)
;INSERT INTO PizzaToppings VALUES(15, 2)
;INSERT INTO PizzaToppings VALUES(15, 17)
;INSERT INTO PizzaToppings VALUES(15, 26)
;INSERT INTO PizzaToppings VALUES(15, 27)
;INSERT INTO PizzaToppings VALUES(16, 1)
;INSERT INTO PizzaToppings VALUES(16, 2)
;INSERT INTO PizzaToppings VALUES(16, 4)
;INSERT INTO PizzaToppings VALUES(16, 5)
;INSERT INTO PizzaToppings VALUES(16, 16)
;INSERT INTO PizzaToppings VALUES(17, 1)
;INSERT INTO PizzaToppings VALUES(17, 2)
;INSERT INTO PizzaToppings VALUES(17, 4)
;INSERT INTO PizzaToppings VALUES(17, 6)
;INSERT INTO PizzaToppings VALUES(17, 16)
;

REPLACE VIEW PizzaView AS
SELECT
  P.Pizza#
 ,P.PizzaName
 ,T.Topping
FROM
  Pizzas P
JOIN
  PizzaToppings PT
ON P.Pizza# = PT.Pizza#
JOIN
  Toppings Z ON PT.Topping# = T.Topping#
;

/***
1. Return all pizzas which are a superset of the searched toppings.

*At least* ('tomaten', 'mozzarella', 'salami') and maybe additional toppings:

Salami, Primavera
***/

/*** 1. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView
WHERE
  Topping IN ('tomatoes', 'mozzarella', 'salami')
GROUP BY 1,2
HAVING COUNT(*) = 3
;

/***
2. Return all pizzas which are a subset of the searched toppings.

*At most* toppings ('tomaten', 'mozzarella', 'salami'), but no other toppings:

Salami, Margherita
***/

/*** 2. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView
GROUP BY 1,2
HAVING
  SUM(CASE WHEN Topping IN ('tomatoes', 'mozzarella', 'salami') THEN 0 ELSE 1 END) = 0
ORDER BY #Toppings DESC
;

/***
3. Return all pizzas which are a exactly made of the searched toppings.

*All toppings* ('tomaten', 'mozzarella', 'salami'), but no other toppings

Salami
***/

/*** 3. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView
GROUP BY 1,2
HAVING
  SUM(CASE WHEN Topping IN ('tomatoes', 'mozzarella', 'salami') THEN 1 ELSE -1 END) = 3
ORDER BY #Toppings
;

/***
4. Return all pizzas which are a superset of the searched toppings.

*At least* toppings ('tomaten' and 'mozzarella') and ('olives' or 'capers')

Diavola, Fantasia, Marinara, Vegetaria
***/

/*** 4. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
  ,SUM(CASE WHEN Topping IN ('olives', 'capers') THEN 1 ELSE 0 END) AS #Optional
FROM PizzaView
GROUP BY 1,2
HAVING
  SUM(CASE WHEN Topping IN ('tomatoes', 'mozzarella') THEN 1 ELSE 0 END) = 2
AND
  #Optional >= 1
ORDER BY 4 DESC
;

/***
5. Return all pizzas which are a superset of the searched toppings.

*At least* toppings ('tomatoes' and 'olives') and maybe additional toppings, but no 'capers'

Marinara, Vegetaria
***/

/*** 5. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView
GROUP BY 1,2
HAVING
  SUM(CASE
        WHEN Topping IN ('tomatoes', 'olives') THEN 1
        WHEN Topping IN ('capers') THEN -1
        ELSE 0
      END) = 2
ORDER BY #Toppings DESC
;

/*** Instead of a list of toppings a table with searched toppings
***/
CREATE SET TABLE searched
(grp INTEGER NOT NULL,
 topping VARCHAR(30) NOT NULL
);

DELETE FROM searched;

INSERT INTO searched VALUES(1,'tomatoes');
INSERT INTO searched VALUES(1,'mozzarella');
INSERT INTO searched VALUES(1,'salami');

/*** 1. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView p
JOIN searched g
ON p.Topping = g.Topping
GROUP BY 1,2
HAVING
  COUNT(*) = (SELECT COUNT(*) FROM searched)
;

/*** 2. ***/
SELECT
  Pizza#
 ,PizzaName
 ,COUNT(*) AS #Toppings
FROM
  PizzaView p
LEFT JOIN searched g
ON p.Topping = g.Topping
GROUP BY 1,2
HAVING
  COUNT(*) = COUNT(g.Topping)
;

/*** 3. ***/
SELECT
  Pizza#
 ,PizzaName
 ,COUNT(*) AS #Toppings
FROM    
  PizzaView p
LEFT JOIN searched g
ON p.Topping = g.Topping
GROUP BY 1,2
HAVING
  SUM(CASE WHEN g.Topping IS NOT NULL THEN 1 ELSE -1 END) 
  = (SELECT COUNT(*) FROM searched)
;

</code>
 </pre>
  
    我从来不需要做＃4 /＃5
    
      搜索
    
     表，但应该可以使用上面的逻辑。
  
</DIV>

2# 小狮子 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”>
  <BLOCKQUOTE>
    
      Kuba Wyrostek在下面建议将每门课程的所有先决条件组合枚举为不同的组合。虽然这可行，但我需要为大约6k行执行此操作，每行包含许多枚举。有没有更有效的方法来实现这一目标？
    
  </BLOCKQUOTE>
  
     
    
存储集是一个明显的选择，我同意库巴。但我建议采用一种不同的方法：
  
   <pre>
 <code>
 prereqs: courses:
+------+------------+ +------+------------+
| p_id | Name | | c_id | Name |
|------|------------| |------|------------|
| 1 | Math | | 1 | Course1 |
| 2 | English | | 2 | Course2 |
| 3 | Art | | 3 | Course3 |
| 4 | Physics | | 4 | Course4 |
| 5 | Psychology | | 5 | Course5 |
+------+------------+ +------+------------+

compound_sets:               compound_sets_prereqs:
+-------+-------+-------+    +-------+-------+
| s_id  | c_id  |  cnt  |    | s_id  | p_id  |
|-------|-------|-------|    |-------|-------|
|   1   |   1   |   1   |    |   1   |   1   |
|   2   |   1   |   2   |    |   1   |   2   |
|   3   |   2   |   1   |    |   2   |   3   |
|   4   |   2   |  null |    |   2   |   4   |
|   5   |   3   |  null |    |   2   |   5   |
+-------+-------+-------+    |   3   |   1   |
                             |   3   |   4   |
                             |   4   |   1   |
                             |   4   |   2   |
                             |   5   |   2   |
                             |   5   |   3   |
                             +-------+-------+

</code>
 </pre>
  
    上面的“cnt”列存储了所需匹配的最小数量，NULL值表示必须匹配所有先决条件。所以在我的例子中我们有以下要求：
  
  
     
    课程1 :(数学或英语）和（艺术，物理和心理学中至少有两门）

课程2 :(数学或物理）和（数学和英语）

课程3：英语和艺术
  
  
    这是SQL：
  
   <pre>
 <code>
 select t.c_id
 , c.name
 from ( select c_id
 , sets_cnt

-- flag the set if it meets the requirements
               , case when matched >= min_cnt then 1 else 0 end  flag

from (  select c.c_id
                         , cs.s_id

-- the number of matched prerequisites
                         , count(p.p_id) matched

-- if the cnt is null - we need
                         -- to match all prerequisites
                         , coalesce( cnt, count(csp.p_id) ) min_cnt

-- the total number of sets the course has
                          , (  select count(1)
                                 from compound_sets t
                                where t.c_id = c.c_id
                            ) sets_cnt

from courses c

join compound_sets cs
                        on cs.c_id = c.c_id

join compound_sets_prereqs csp
                        on cs.s_id = csp.s_id

left join (  select p_id
                                     from prereqs p
                                    -- this data comes from the outside
                                    where p.name in ( 'Physics',
                                                      'English',
                                                      'Math',
                                                      'Psychology' )
                                ) p
                        on csp.p_id = p.p_id

group by c.c_id, cs.s_id, cs.cnt
                 ) t
       ) t
     , courses c
 where t.c_id = c.c_id 
 group by t.c_id, c.name, sets_cnt

-- check that all sets of this course meet the requirements
having count( case when flag = 1 then 1 else null end ) = sets_cnt

</code>
 </pre>
</DIV>