java - 对如何有效地形成此 SQL 查询感到困惑

Question

我正在尝试用 SQL(在 Java 程序中)有效地构造一个查询，以查找高中类(class)的“历史先决条件”。

基本上，我有一个名为 PREREQUISITE 的表，它由元组 (Class_name, Precession_name) 组成。然而，“历史先决条件”被定义为所有先决条件，以及所有先决条件的先决条件等等。

目前，我正在考虑为每个模块创建一个新表，使用 Java 中的深度优先搜索来查找先前的先决条件等，但我认为这最终效率非常低。如果有人能提供有关如何理清我的想法的指示，我将非常感激。

如果这很重要的话，我正在使用 Oracle。

例如示例数据:

Class_name              Prerequisite_name

EC104                   EC101  
EC106                   EC104  
EC210                   EC106  

应提供输出:

EC101:  
EC104: EC101  
EC106: EC101, EC104  
EC210: EC101, EC104, EC106  

Answer 1

为了详细说明 RJD 的答案，以下是在 Oracle 中执行此操作的方法。

WITH -- PREREQUISITES is just test data that you should have in your table already.. you don't need this part if you have actual tables.
     prerequisites AS
       (SELECT 'EC104' class_name,
               'EC101' prerequisite_name
        FROM   DUAL
        UNION ALL
        SELECT 'EC106',
               'EC104'
        FROM   DUAL
        UNION ALL
        SELECT 'EC210',
               'EC106'
        FROM   DUAL),
     -- CLASSES gets a distinct list of classes, which hopefully you already have in another table.. you don't need this part if you have tables
     classes AS
       (SELECT class_name FROM prerequisites
        UNION
        SELECT prerequisite_name FROM prerequisites),
     -- HIERARCHY gets the historical dependencies -- you need this part
     hierarchy AS
       (SELECT p.*,
               CONNECT_BY_ROOT class_name AS root_class_name
        FROM   prerequisites p
        CONNECT BY class_name = PRIOR prerequisite_name)
SELECT c.class_name,
       LISTAGG (h.prerequisite_name, ',') WITHIN GROUP (ORDER BY h.prerequisite_name)
FROM   classes c LEFT JOIN hierarchy h ON h.root_class_name = c.class_name
GROUP BY c.class_name
ORDER BY c.class_name