部件分解问题

部件分解问题是递归的典型应用。在此问题中，组合特定对象所必需的组件由图形表示。目标是使用数据库表表示此图形，然后计算必需元素部件的总数。

例如，下图显示了一个简单书架的各个组件。书架由三层架板、一块背板和四条腿组成，四条腿用四个螺钉固定。每块架板用四个螺钉固定。背板用八个螺钉固定。

下表中的信息表示书架图形的边。第一列命名组件，第二列命名该组件的其中一个子组件，第三列指定需要多少个子组件。

component	subcomponent	quantity
bookcase	back	1
bookcase	side	2
bookcase	shelf	3
bookcase	foot	4
bookcase	screw	4
back	backboard	1
back	screw	8
side	plank	1
shelf	plank	1
shelf	screw	4

执行以下语句创建书架表并插入组件和子组件数据。

CREATE TABLE bookcase (
      component      VARCHAR(9),
      subcomponent   VARCHAR(9),
      quantity       INTEGER,
    PRIMARY KEY ( component, subcomponent )
); 
INSERT INTO bookcase
  SELECT 'bookcase', 'back',      1 UNION
  SELECT 'bookcase', 'side',      2 UNION
  SELECT 'bookcase', 'shelf',     3 UNION
  SELECT 'bookcase', 'foot',      4 UNION
  SELECT 'bookcase', 'screw',     4 UNION
  SELECT 'back',     'backboard', 1 UNION
  SELECT 'back',     'screw',     8 UNION
  SELECT 'side',     'plank',     1 UNION
  SELECT 'shelf',    'plank',     1 UNION
  SELECT 'shelf',    'screw',     4;

执行以下语句生成由组件、子组件以及装配书架所需的数量组成的列表。

SELECT * FROM bookcase
ORDER BY component, subcomponent;

执行以下语句生成由子组件以及装配书架所需的数量组成的列表。

WITH RECURSIVE parts ( component, subcomponent, quantity ) AS
( SELECT component, subcomponent, quantity
  FROM bookcase WHERE component = 'bookcase'
    UNION ALL
  SELECT b.component, b.subcomponent, p.quantity * b.quantity
  FROM parts p JOIN bookcase b ON p.subcomponent = b.component )
SELECT subcomponent, SUM( quantity ) AS quantity
FROM parts
WHERE subcomponent NOT IN ( SELECT component FROM bookcase )
GROUP BY subcomponent
ORDER BY subcomponent;

下面显示了此查询的结果。

subcomponent	quantity
backboard	1
foot	4
plank	5
screw	24

或者，您也可以重写此查询以执行其它级别的递归，从而消除在主 SELECT 语句中使用子查询的需要。以下查询的结果与上一个查询的那些结果相同。

WITH RECURSIVE parts ( component, subcomponent, quantity ) AS
( SELECT component, subcomponent, quantity
  FROM bookcase WHERE component = 'bookcase'
    UNION ALL
  SELECT p.subcomponent, b.subcomponent,
    IF b.quantity IS NULL
    THEN p.quantity
    ELSE p.quantity * b.quantity
    ENDIF
  FROM parts p LEFT OUTER JOIN bookcase b
  ON p.subcomponent = b.component
    WHERE p.subcomponent IS NOT NULL
 )
SELECT component, SUM( quantity ) AS quantity
FROM parts
WHERE subcomponent IS NULL
GROUP BY component
ORDER BY component;

使用DocCommentXchange讨论此页。