OracleのSQL発行で ツリー構造のデータを表示させたいが 最上位の表示方法に困っている

Question

当社製造業なのですが、よくある基幹システムのように、製品構成マスタというものを有しており
製造品に対する利用子部品を、親子関係（一階層下のみ）で定義づけています。

左図は　Oracle内の製品構成マスタというテーブルで、一レコードによって　製造品と利用部品の親子関係を定義している様子、
右図は　それらレコードの組合わせ＝幾つかの製造品を更に組み合わせることで　はじめて市場出荷される最終製品が仕上がることをイメージ頂くために　ご用意させて頂いたものです。

【困っていること】

指定の品目（検索の祖先）を　交えた表示を達成したいのですが、指定値が複数の場合の対処方法が分からずに困っています。

【自分が試したこと】

こちらの記事を参考に、本日初めて　CONNECT BY　やら　START WITHを使って　以下の発行文を作成しました。
単独指定の場合（以下発行分はIN句なのに単独指定）は　一応に結果を得られるのですが
本来のIN句らしく　幾つかの製造品目をこの括弧内に指定したい、というのが望みです！！

（出荷を取りやめた最終製品の一覧から　不要な製造中間体と購入原料を一気に抽出したい！　　■20210831 1613追記■ 実行時の指定は　最終製品と限らず　自身が子部品として位置付けられた製造中間体も含みます。）

SELECT B.親品目コード 検索指定, A.親品目コード, A.子品目コード FROM 
(SELECT 親品目コード, 子品目コード FROM 製品構成マスタ START WITH 親品目コード IN ('A001') CONNECT BY PRIOR 子品目コード=親品目コード) A 
LEFT OUTER JOIN 
(SELECT DISTINCT 親品目コード FROM 製品構成マスタ WHERE 親品目コード IN ('A001')) B ON 1=1

どなたか対応法を存じ上げていたら教えて頂けたら幸いです、よろしくお願いします。

Comments

[Orlofsky]

何度も書いていると思うけど、質問に使っているテーブルのCREATE TABLE文とINSERT文を何件か追加できた方がコメントが付き易いかと。

Answer 1

以下のようなクエリで実現できるかと思います。

SELECT CONNECT_BY_ROOT 親品目コード, 親品目コード,  子品目コード
FROM 製品構成マスタ
START WITH 親品目コード IN ('A001', 'A101')
CONNECT BY PRIOR 子品目コード = 親品目コード;

あるいはWITHによる再帰SQLを利用して、以下のように書くこともできます。

WITH R (検索指定, 親品目コード,  子品目コード) AS (
    SELECT 親品目コード, 親品目コード,  子品目コード
    FROM 製品構成マスタ 
    WHERE 親品目コード IN ('A001', 'A101')
  UNION ALL 
    SELECT R.検索指定, S.親品目コード, S.子品目コード
    FROM 製品構成マスタ S
    JOIN R ON S.親品目コード = R.子品目コード
)
SELECT * FROM R;

Comments

[saya24]

neko_the_shadowさん、お時間を頂きありがとうございます。
手元で確認したところ、重複した行が返ってきてしまいました。　上下両方ともです。

まずはご報告まで

[neko_the_shadow]

重複した行というのは具体的にどのようなものでしょうか? その事象は提示いただいているサンプルデータにおいて発生しますか?

[mayu-]

私もneko_the_shadowさんのSQLを実施して
saya24さんご希望の結果セットが返ってくるのが確認できました。
connect_by_root演算子で簡単に記述できましたね。素敵です。

[saya24]

申し訳ありません、重複の事象発生は　社内の実データで確認していたならではの状況でした。混乱を与え申し訳ございませんでした。(使用開始日と使用停止日の別フィールドで、同一親子品目関係のレコードを期間違いで保持できるようになっている。使用料とか季節で違う)

で、単純に　大大大満足な結果を得られて　非常に感激しています！！！
いくら感謝しても感謝しきれません、この発行文は絶対幅広い分野で活用していけるとも今から確信しております。本当にありがとうございました。

Answer 2

指定の品目（検索の祖先）を　交えた表示を達成したいのですが、
指定値が複数の場合の対処方法が分からずに困っています。

ご提示いただいた製品構成マスタテーブルのサンプルデータには
ルートノード( 検索指定列に表示されているA001 )を特定できるレコードが見当たらない...ですね。

そのため、ご希望の抽出方法を実装するためにはSQL文において
「 親品目コードには存在するけれども、子品目コードには存在しない 」
という条件に合致した品目コードをルートノードとして定義する必要があるでしょう。

以下のような感じでどうでしょうか。
( WITH句でのルートノード特定方法がとてつもなくダサいのはご容赦 )

WITH saya24 ( parent, child, path, hierarchy, root ) AS
(
    SELECT parent
         , child
         , sys_connect_by_path( q.child, '/' )
         , level
         , case
               when level = 1 
               then
                   substr( sys_connect_by_path( q.child, '/' ), 2 )
               else
                   substr( sys_connect_by_path( q.child, '/' )
                     , 2
                     , instr( sys_connect_by_path( q.child, '/' ), '/', 1, 2 ) - 2
                   )
           end
    FROM
    (
        SELECT 子品目コード child, 親品目コード parent
        FROM 製品構成マスタ
        
        UNION ALL
        
        SELECT 親品目コード, 'root'
        FROM 製品構成マスタ
        MINUS
        SELECT 子品目コード, 'root'
        FROM 製品構成マスタ
    ) q
    start with parent = 'root'
    connect by prior child = parent
)

SELECT * 
  FROM saya24
 WHERE root IN ( 'A001' )
;

　
■結果

parent　child　　　　path　　　　　hierarchy　　 root
------------------------------------------------------
root　　A001　　/A001　　　　　　　　　1　　　　A001
A001　　B001　　/A001/B001　　　　　　 2　　　　A001
B001　　C002　　/A001/B001/C002　　　　3　　　　A001
C002　　F002　　/A001/B001/C002/F002　 4　　　　A001
C002　　F003　　/A001/B001/C002/F003　 4　　　　A001
C002　　G001　　/A001/B001/C002/G001　 4　　　　A001
B001　　C003　　/A001/B001/C003　　　　3　　　　A001
B001　　D001　　/A001/B001/D001　　　　3　　　　A001
A001　　B002　　/A001/B002　　　　　　 2　　　　A001
B002　　D002　　/A001/B002/D002　　　　3　　　　A001
B002　　D003　　/A001/B002/D003　　　　3　　　　A001
B002　　E001　　/A001/B002/E001　　　　3　　　　A001
A001　　B101　　/A001/B101　　　　　　 2　　　　A001
B101　　C102　　/A001/B101/C102　　　　3　　　　A001
B101　　C103　　/A001/B101/C103　　　　3　　　　A001
B101　　D101　　/A001/B101/D101　　　　3　　　　A001
A001　　B102　　/A001/B102　　　　　　 2　　　　A001
A001　　C001　　/A001/C001　　　　　　 2　　　　A001
C001　　E002　　/A001/C001/E002　　　　3　　　　A001
C001　　E003　　/A001/C001/E003　　　　3　　　　A001
C001　　F001　　/A001/C001/F001　　　　3　　　　A001
A001　　C101　　/A001/C101　　　　　　 2　　　　A001

Comments

[saya24]

mayuさん、ご回答をありがとうございます。一読しただけでございますが
発行結果を得ようとする際の　指定値は　最上位とは　限らないのですが　対応できるご提案でしょうか？　文面をみて　まず気になりました

原価が高い工程品＝製造中間体を指定し　原材料を見直すなど、ほんと　どこの位置の品目を指定されるかわからないものを作ろうとしています。

発行文を期待しておりますが、ご提案がWITHから始まっておりますね？、初めて拝見いたしましたが　SQLPLUSなどで　正常に発行できるのでしょうか、
無知ですみません．．．．

[mayu-]

> 指定値は　最上位とは　限らないのですが　対応できるご提案でしょうか

下の階層でしたら比較的容易に対応できるでしょう。
( ご提示のテーブル構造では「 最上位 」の複数指定が一番難しい )

私が記述したSQL文で申し上げるなら
hierarchy と parent の両フィールドに適宜、抽出条件を記述します。

なお、指定する階層が「 最上位とは限らない 」という重要な文言は
コメント欄ではなく、予めご質問に明記しておくことをお薦めします。

なにはともあれ、あれもこれもと一気に解決しようとするのではなく
問題点を１点に絞り、一歩一歩 完成に近づくようになさったほうがいいでしょう。

> ご提案がWITHから始まっておりますね？、
> 初めて拝見いたしましたが　SQLPLUSなどで　正常に発行できるのでしょうか

ご利用のバージョンが 9i以降 でしたら使えます。
SQL*Plusでも発行可能です。

ちなみに 共通テーブル式( WITH )で記述している部分は
FROM句のインラインビューとして記述する方法もありますから
興味がおありなら、ご自身でチャレンジなさってみるといいでしょう。

[saya24]

大事なこと=最上位の指定とは限らない　ことの本文への記載を漏らしてしまい　申し訳ございませんでした。試してみたところ仰るように　それ前提の作りのご提案であり　手を加える必要がある、ということですね。失敗！！
工程途中の製造品を指定できるように　まずは理解を進めます。