12,673
回編集
差分
ナビゲーションに移動
検索に移動
CREATE C[[R]]EATE TABLE PARTLISTPA[[R]]TLIST (PART VARCHARPA[[R]]T VA[[R]]CHA[[R]](8), SUBPART VARCHARSUBPA[[R]]T VA[[R]]CHA[[R]](8), QUANTITY QU[[ANT]]ITY INTEGER);
PART PA[[R]]T SUBPART SUBPA[[R]]T Total Qty Used
編集の要約なし
==[[DB2 再帰]]==[[DB2]] | {{category [[Category:SQL}}]]
*[http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059242.html?cp=SSEPGG_10.1.0%2F2-9-6-3-0-0&lang=ja 再帰の例: 部品表からのメモ]
===前提===
====表====
====データ====
PART SUBPART QUANTITYQU[[ANT]]ITY
-------- -------- -----------
00 01 5
*直接の副部品、副部品の副部品などが入る
<blockquote>ある部品が何回も使用される場合でも、その副部品は 1 回しかリストに示されません</blockquote>
WITH RPL (PART, SUBPART, QUANTITYQU[[ANT]]ITY) AS ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITYQU[[ANT]]ITY FROM PARTLIST ROOTF[[R]]OM PA[[R]]TLIST [[R]]OOT WHERE ROOTWHE[[R]]E [[R]]OOT.PART PA[[R]]T = '01'
UNION ALL
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITYQU[[ANT]]ITY FROM RPL PARENTF[[R]]OM [[R]]PL PA[[R]]ENT, PARTLIST PA[[R]]TLIST CHILD WHERE WHE[[R]]E PARENTPA[[R]]ENT.SUBPART SUBPA[[R]]T = CHILD.PARTPA[[R]]T
)
SELECT DISTINCT PART, SUBPART, QUANTITYQU[[ANT]]ITY FROM RPLF[[R]]OM [[R]]PL ORDER BY PART, SUBPART, QUANTITYQU[[ANT]]ITY;
=====共通表式=====
*RPL [[R]]PL という名前で指定されている
*照会の再帰的な部分が表されている
=====初期化全選択=====
*UNION の第 1 オペランド (全選択)
*部品 '01' の直接の子が求まります
*この最初の全選択の結果が、 共通表式 RPL [[R]]PL (再帰的 PARTLISTPA[[R]]TLIST) の中に入れられる
*この例の場合、UNION は常に UNION ALL でなければならない
=====副部品の副部品を計算=====
*FROM F[[R]]OM 節で共通表式 RPL [[R]]PL とソース表 (CHILD: 子) の部品を、 RPL [[R]]PL (PARENTPA[[R]]ENT: 親) に入っている現行の結果の副部品に結び付ける*結果は、再度 RPL [[R]]PL に入れられる
*UNION の第 2 オペランドは、子が存在しなくなるまで繰り返し使用される
=====SELECT DISTINCT=====
=====照会結果=====
PART SUBPART QUANTITYQU[[ANT]]ITY
-------- -------- -----------
01 02 2
*単一レベル正展開と異なる主な点は、数量を集計する必要があるということ
WITH RPL (PART, SUBPART, QUANTITYQU[[ANT]]ITY) AS
(
SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITYQU[[ANT]]ITY FROM PARTLIST ROOTF[[R]]OM PA[[R]]TLIST [[R]]OOT WHERE ROOTWHE[[R]]E [[R]]OOT.PART PA[[R]]T = '01'
UNION ALL
SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITYQU[[ANT]]ITY*CHILD.QUANTITYQU[[ANT]]ITY FROM RPL PARENTF[[R]]OM [[R]]PL PA[[R]]ENT, PARTLIST PA[[R]]TLIST CHILD WHERE PARENTWHE[[R]]E PA[[R]]ENT.SUBPART SUBPA[[R]]T = CHILD.PARTPA[[R]]T
)
SELECT PART, SUBPART, SUM(QUANTITYQU[[ANT]]ITY) AS "Total QTY Used" FROM RPLF[[R]]OM [[R]]PL GROUP G[[R]]OUP BY PARTPA[[R]]T, SUBPARTSUBPA[[R]]T ORDER O[[R]]DE[[R]] BY PARTPA[[R]]T, SUBPARTSUBPA[[R]]T;
=====数量の合計=====
*RPL [[R]]PL という名前で指定されている再帰的共通表式の中の UNION の第 2 オペランドの選択リストによって、数量の合計が示されている
*副部品の使用量を求めるには、親の数量に、親 1 個当たりの子の数量を乗算
=====最終的な集計=====
*1 つの部品が異なる複数のロケーションで何回も使用される場合、共通表式 RPL [[R]]PL をグループ化し、 主要全選択の選択リストの中で SUM 集約関数を使用することで、最終的な集計を行う
=====照会結果=====
-------- -------- --------------
01 02 2
===深さの制御===
*レベル番号を結果に組み入れる
WITH RPL (LEVEL, PART, SUBPART, QUANTITYQU[[ANT]]ITY) AS
(
SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITYQU[[ANT]]ITY FROM PARTLIST ROOTF[[R]]OM PA[[R]]TLIST [[R]]OOT WHERE ROOTWHE[[R]]E [[R]]OOT.PART PA[[R]]T = '01'
UNION ALL
SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITYQU[[ANT]]ITY FROM RPL PARENTF[[R]]OM [[R]]PL PA[[R]]ENT, PARTLIST PA[[R]]TLIST CHILD WHERE PARENTWHE[[R]]E PA[[R]]ENT.SUBPART SUBPA[[R]]T = CHILD.PARTPA[[R]]T AND PARENTPA[[R]]ENT.LEVEL < 2
)
SELECT PART, LEVEL, SUBPART, QUANTITYQU[[ANT]]ITY FROM RPLF[[R]]OM [[R]]PL;
=====LEVELの初期化=====
*初期化全選択では、LEVEL 列の値を 1 に初期化
*結果のレベル数を制御するため、2 番目の全選択に、 親のレベルが 2 未満でなければならないという条件
=====照会結果=====
PART LEVEL SUBPART QUANTITYQU[[ANT]]ITY
-------- ----------- -------- -----------
01 1 02 2
© 2006 矢木浩人
