ITエンジニア日記 ～NO SKILL, NO LIFE～

学んだ技術や、気になることをアウトプットしていきます。プログラミング, インフラ, etc...

トップ > SQL > CTE（共通テーブル式）の再帰問い合わせ

CTE（共通テーブル式）の再帰問い合わせ

SQL

BOM(部品表)について調べてたら、CTE（共通テーブル式）の再帰問い合わせというSQLが出てきたので勉強がてらまとめてみます。

[目次]

1. CTE再帰問い合わせ

CTE（共通テーブル式）は副問い合わせの代わりに使ったりすると、SQLの可読性が上がるためよく使ってます。 CTE再帰問い合わせは、データベースのテーブルの木構造の親子関係をもったレコードを再帰的に取得できるそうです。

下記のテーブルでCTE再帰問い合わせを試してみます。 PARTは親、SUBPARTは子、QUANTITY は子の数量です。

PARTLISTテーブル

PART SUBPART QUANTITY
1 ProductA Parts1 1
2 ProductA Parts2 1
3 ProductA Parts3 3
4 ProductA Parts4 2
5 Parts1 Parts5 1
6 Parts1 Parts6 2
7 Parts2 Parts7 1
8 Parts2 Parts8 2
9 Parts4 Parts9 10
10 Parts8 Parts6 4

親子関係を図で書くと次のような感じ。

f:id:masakiXX0:20191027212301j:plain

1.1. 実際にSQLを書いてみる

PARTLISTテーブルに対して、CTE再帰問い合わせするSQLを書いてみます。このとき、子は「総数量＝親.QUANTITY * 子.QUANTITY」の計算式で総数量を求めます。

WITH RoopPartsList (PART, SUBPART, QUANTITY) AS
(
  SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
  FROM PARTLIST ROOT
  WHERE ROOT.PART = 'ProductA'
  UNION ALL
  SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY * PARENT.QUANTITY
  FROM RoopPartsList PARENT, PARTLIST CHILD
  WHERE PARENT.SUBPART = CHILD.PART
)
SELECT PART, SUBPART, QUANTITY
FROM RoopPartsList
;

初めにWITH句内の一つ目のSELECT文で親（図だと一番上のProductA）を取得してRoopPartsList に格納します。次に、2つ目のSELECT文でRoopPartsList と PARTLIST を結合し、子の情報を取得してRoopPartsList に格納します。以降、2つ目のSELECT文を繰り返し、子が見つからなくなるまで実行します。

WITH句の外のSELECT文で、最終的なRoopPartsList の結果を取得しています。

SQLの実行結果は下記です。

PART SUBPART QUANTITY
1 ProductA Parts1 1
2 ProductA Parts2 1
3 ProductA Parts3 3
4 ProductA Parts4 2
5 Parts4 Parts9 20
6 Parts2 Parts7 1
7 Parts2 Parts8 2
8 Parts8 Parts6 8
9 Parts1 Parts5 1
10 Parts1 Parts6 2

親⇒子と辿って、総数量も求められていることがわかります。

1.2. 取得する階層数を指定する

取得したい階層の深さを指定するSQLは下記のものです。LEVELという項目を追加し、トップレベルの親は「1」を指定し、子は「親のLEVEL + 1」とすることで、階層を取得します。下記のSQLでは2階層目までの結果を取得しています。

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

SQLの実行結果は下記です。

LEVEL PART SUBPART QUANTITY
1 1 ProductA Parts1 1
2 1 ProductA Parts2 1
3 1 ProductA Parts3 3
4 1 ProductA Parts4 2
5 2 Parts4 Parts9 20
6 2 Parts2 Parts7 1
7 2 Parts2 Parts8 2
8 2 Parts1 Parts5 1
9 2 Parts1 Parts6 2

3階層目のParts6（親はParts8）が取得されていないことがわかります。

2. まとめ

CTE（共通テーブル式）の再帰問い合わせを実際にSQLを作って試してみました。

参考