階層問合せ

　組織表や部品表など、階層構造を持つデータを扱いたいときに悩むので基本的なことを記しておきます。

関係性を示すテーブルの構造

　なお、利用するデータベースによって使えるSQL構文が異なるようです。

SQLserver

　MicrosoftのSQLserverでやってみました。

WITH temptable AS (
    SELECT
          1 AS lv
        , child
        , parent
        , CAST(child AS nvarchar(MAX)) AS path
    FROM connection AS t
    WHERE parent IS NULL
    UNION ALL
    SELECT
        p.lv + 1 AS lv
        , c.child
        , c.parent
        , p.path + '-' + c.child AS path
    FROM temptable AS p
    INNER JOIN connection AS c ON p.child = c.parent
)
SELECT
    lv
    , child
    , parent
    , path
FROM temptable

　このSQLを実行すると、下の結果が得られます。

　このSQL文のポイントは、非再起と再起の部分です。再起とは自分で自分自身を呼び出している繰り返しのことです。

    SELECT
          1 AS lv
        , child
        , parent
        , CAST(child AS nvarchar(MAX)) AS path
    FROM connection AS t
    WHERE parent IS NULL

　まず、非再起のSQL構文で、親となる情報をconnection（関係性を示すテーブル）から抽出しています。

　CAST(child AS nvarchar(MAX))を行っているのは、ここで型変換でエラーが発生してしまうためです。

　parent IS NULLで親を縛っているので、ここをchild = ‘2’と書き換えると、childが2以降の階層を抽出できるようになります。

    SELECT
        p.lv + 1 AS lv
        , c.child
        , c.parent
        , p.path + '-' + c.child AS path
    FROM temptable AS p
    INNER JOIN connection AS c ON p.child = c.parent

　非再起で抽出された情報（temptable）を基に、関係性（connection）を辿って（INNER JOIN connection AS c ON p.child = c.parent）階層を進んでいます。templtableを再起している部分です。

ORACLE

　ORACLEには「階層問合せ」というものが用意されているらしく、意外と簡単にできるらしいのです。らしいというのは、これを書いている2022/4/20時点では実際に試していないから。2022/4/21に実際に試す機会があったので試してみたら、階層問合せができました。

SELECT
      child
    , parent
    , LEVEL
FROM connection
START WITH
     parent IS NULL
CONNECT BY
     PRIOR child = parent

　だいぶ、簡単な構文になるようです。残念ながらSQLserverではエラーになってしまいました。

　しかし、階層問合せなだけであって、再起処理で書いたpathの様な親の要素に子の要素を足していくような処理はできないようです。そういったデータが欲しい場合は、ORACLEでも再起処理を実行しなくてはなりません。

　ORACLEとSQLserverのSQLの書き方の違いで少し悩まされたのは内緒です。

まとめ

　じゃあ、これが何の役に立つの？ってなると、ここで求められた階層情報に、更に何らかの情報を足していかなくてはなりません。

• 技術手帳
• ＳＱＬ