递归示例：材料清单

在许多业务环境中，通常需要使用材料清单 (BOM) 应用程序。为了演示 BOM 应用程序的递归公共表表达式的功能，请考虑包含相关子部件的部件表以及部件所需的子部件数量。

在此示例中，按如下所示创建表：

  CREATE TABLE PARTLIST
                 (PART VARCHAR(8),
                  SUBPART VARCHAR(8),
                  QUANTITY INTEGER);

为了演示此示例的查询结果，假设 PARTLIST 表填充了以下值：

  PART     SUBPART  QUANTITY
  -------- -------- -----------
  00       01                 5
  00       05                 3
  01       02                 2
  01       03                 3
  01       04                 4
  01       06                 3
  02       05                 7
  02       06                 6
  03       07                 6
  04       08                10
  04       09                11
  05       10                10
  05       11                10
  06       12                10
  06       13                10
  07       14                 8
  07       12                 8

示例 1：单层展开

第一个示例称为“单层展开”。它回答了以下问题：需要哪些部件才能构建由“01”标识的部件？。该列表包含直接子部件、子部件的子部件等。但是，如果多次使用某个部件，那么此部件的子部件只会列出一次。

WITH RPL (PART, SUBPART, QUANTITY) AS
     (  SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
        FROM PARTLIST ROOT
        WHERE ROOT.PART = '01'
      UNION ALL
        SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
        FROM RPL PARENT, PARTLIST CHILD
        WHERE  PARENT.SUBPART = CHILD.PART
     )
SELECT DISTINCT PART, SUBPART, QUANTITY
 FROM RPL
  ORDER BY PART, SUBPART, QUANTITY;

上述查询包含一个由名称 RPL 标识的公共表表达式（表示此查询的递归部件）。它说明了递归公共表表达式的基本元素。

UNION 的第一个操作数（全查询）称为初始化全查询，用于获取部件“01”的直接子代。此全查询的 FROM 子句引用了源表，并且从不引用自身（在此情况下为 RPL）。第一个全查询的结果将进入公共表表达式 RPL（递归 PARTLIST）。如此示例中所示，UNION 必须始终是 UNION ALL。

UNION 的第二个操作数（全查询）使用 RPL 来计算子部件的子部件，方法是使 FROM 子句引用公共表表达式 RPL 和源表，并将源表（子表）的一个部件与 RPL（父表）中当前结果的子部件连接在一起。结果再次返回到 RPL。然后重复使用 UNION 的第二个操作数，直到不再存在子表。

此查询的主全查询中的 SELECT DISTINCT 可确保不会多次列出同一部件/子部件。

该查询的结果为如下所示：

  PART     SUBPART  QUANTITY
  -------- -------- -----------
  01       02                 2
  01       03                 3
  01       04                 4
  01       06                 3
  02       05                 7
  02       06                 6
  03       07                 6
  04       08                10
  04       09                11
  05       10                10
  05       11                10
  06       12                10
  06       13                10
  07       12                 8
  07       14                 8

在结果中观察到部件“01”变为部件“02”，然后又变为部件“06”，依此类推。此外，请注意两次到达部件“06”，一次是直接通过部件“01”，另一次是通过部件“02”。但是，在输出中，它的子组件仅在需要时列出一次（这是使用 SELECT DISTINCT 的结果）。

请务必记住，如果使用递归公共表表达式，可能会引入无限循环。在此示例中，如果用于连接父表和子表的第二个操作数的搜索条件编码为以下内容，那么将产生一个无限循环：

   PARENT.SUBPART = CHILD.SUBPART

这个会导致无限循环的示例显然不会编码预期内容。请谨慎确定要编码的内容，确保递归循环存在明确的结束。

可以在应用程序中生成此示例查询所生成的结果，而无需使用递归公共表表达式。但是，这种方法需要为每个递归级别启动一个新查询。此外，应用程序需要将所有结果放回数据库中，以便对结果进行排序。这种方法会使应用程序逻辑复杂化并且性能不佳。对于其他材料清单查询（例如，汇总和缩行展开查询），应用程序逻辑将变得更加复杂和低效。

示例 2：汇总展开

第二个示例是汇总展开。这里提出的问题是：构建部件“01”所需的各个部件的总数量是多少？与单层展开的主要区别在于需要汇总数量。第一个示例指示部件在必要时所需的子部件的数量。它没有指示构建部件“01”所需的子部件数量。

WITH RPL (PART, SUBPART, QUANTITY) AS
   (
      SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
       FROM PARTLIST ROOT
       WHERE ROOT.PART = '01'
    UNION ALL
      SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
       FROM RPL PARENT, PARTLIST CHILD
       WHERE PARENT.SUBPART = CHILD.PART
   )
SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
 FROM RPL
  GROUP BY PART, SUBPART
  ORDER BY PART, SUBPART;

在上述查询中，递归公共表表达式中 UNION 的第二个操作数的选择列表（由名称 RPL 标识）显示了数量的汇总。要了解使用的子部件数量，可用父代的数量乘以子代的每个父代的数量。如果在不同位置多次使用某个部件，那么需要执行另一次最终汇总。要执行此操作，需要对公共表表达式 RPL 进行分组，并在主全查询的选择列表中使用 SUM 聚集函数。

该查询的结果为如下所示：

  PART     SUBPART  Total Qty Used
  -------- -------- --------------
  01       02                    2
  01       03                    3
  01       04                    4
  01       05                   14
  01       06                   15
  01       07                   18
  01       08                   40
  01       09                   44
  01       10                  140
  01       11                  140
  01       12                  294
  01       13                  150
  01       14                  144

查看输出以确定子部件“06”对应的行。使用的总数量值 15 是通过直接用于部件“01”的数量 3 和用于部件“02”的数量 6（这是部件“01”的两倍）来计算得出的。

示例 3：控制深度

这里可能提出的问题：当表中的部件级别比您的查询感兴趣的级别多时会发生什么情况？即，如何编写查询来回答以下问题：构建由“01”标识的部件所需的前两级部件有哪些？为了在示例中清楚起见，该级别已包含在结果中。

WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS
      (
         SELECT 1,               ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
          FROM PARTLIST ROOT
          WHERE ROOT.PART = '01'
       UNION ALL
         SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
          FROM RPL PARENT, PARTLIST CHILD
          WHERE PARENT.SUBPART = CHILD.PART
            AND PARENT.LEVEL < 2
      )
 SELECT PART, LEVEL, SUBPART, QUANTITY
   FROM RPL;

此查询类似于示例 1。已引入了列 LEVEL 来计算来自原始部件的级别数量。在初始化全查询中，LEVEL 列的值将初始化为 1。在后续全查询中，来自父代的级别递增 1。然后，要控制结果中的级别数，第二个全查询包含父级别必须小于 2 的条件。这可确保第二个全查询仅处理到第二个级别的子代。

查询的结果为：

  PART     LEVEL       SUBPART  QUANTITY
  -------- ----------- -------- -----------
  01                 1 02                 2
  01                 1 03                 3
  01                 1 04                 4
  01                 1 06                 3
  02                 2 05                 7
  02                 2 06                 6
  03                 2 07                 6
  04                 2 08                10
  04                 2 09                11
  06                 2 12                10
  06                 2 13                10