SQL 解説書

例 2: 合計型展開

2 番目の例は、合計型展開の例です。 ここでの質問は、「部品 '01' の作成には各部品が合計どれくらい必要か」というものです。 単一レベル展開と異なる主な点は、数量を集計する必要があるということです。 例 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 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;

上記の照会では、 RPL という名前で指定されている再帰的共通表式の中の UNION の第 2 オペランドの 選択リストによって、数量の合計が示されています。 副部品の使用量を求めるには、親の数量に、親 1 個当たりの子の数量を乗算します。 1 つの部品が異なる複数の場所で何回も使用される場合は、 もう 1 つ最終的な集計が必要になります。 これは、共通表式 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' の数 (2) を掛けたものとを加えた数です。


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]