再帰処理[CTE]のやり方-SQLServer編-

再帰処理[CTE]のやり方-SQLServer編-

SQLServerでの再帰処理(以下CTE)について実装する機会があったのでその記録

生産管理で良くある、製品に対して部品は何?何個必要?を製品のコードがあれば取得出来ちゃうようにしたかった!

実装例

コード

CREATE FUNCTION FN_CTE(
  @argKEY AS VARCHAR
  ,@argQTY AS FLOAT
  ,@argMaxLayerLevel AS INT
) RETURNS TABLE AS RETURN (
  WITH CTE AS (
    SELECT 
        1 AS LayerLevel
      , E.Item
      , E.ChildItem
      , E.ChildItemQTY
      , @argQTY * E.ChildItemQTY AS CalcChildItemQTY
    FROM EBOM E
    WHERE E.Item = @argKEY
    UNION ALL 
    SELECT 
        CTE.LayerLevel + 1 AS LayerLevel
      , E_CHILD.Item
      , E_CHILD.ChildItem
      , E_CHILD.ChildItemQTY
      , CTE.CalcChildItemQTY * E_CHILD.ChildItemQTY AS CalcChildItemQTY
    FROM CTE
    INNER JOIN EBOM E_CHILD
    ON E_CHILD.ChildItem = CTE.Item 
    WHERE CTE.LayerLevel < @argMaxLayerLevel
  )
  SELECT
      MAIN.LayerLevel
    , MAIN.Item
    , MAIN.ChildItem
    , MAIN.ChildItemQTY
    , MAIN.CalcChildItemQTY
  FROM CTE MAIN
)

データ

EBOM

ItemChildItemChildItemQTY
ITEM-001ITEM-00210
ITEM-001ITEM-00315
ITEM-002ITEM-0042
ITEM-003ITEM-0051
ITEM-004ITEM-00620

実行・結果

SELECT * FROM FN_CTE('ITEM_001', 2, 10)

Result

LayerLevelItemChildItemChildItemQTYCalcChildItemQTY
1ITEM-001ITEM-0021020
1ITEM-001ITEM-0031530
2ITEM-002ITEM-004240
2ITEM-003ITEM-005130
3ITEM-004ITEM-00620800

感じた事

ストアドで テーブル型 として定義しているため、複数のKey値から一括でデータを取得することに向かない印象です。

引数を指定しているということもあるでしょうが。。。

Key値のみを別テーブルに登録しておいて、View扱いで取得も可能でしょうが 毎回全件取得するなんてありえない ですしね。

単一Key値用と複数Key値用の2パターンを作る案もありますが、別関数になって管理面倒ですし。結局のところ、レスポンスと相談ですね

技術カテゴリの最新記事