EasyWork-SYStem

もっと簡単にしてよ!を叶えたい

階層問合せ

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

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

childparent
1NULL
21
32
connection

 なお、利用するデータベースによって使える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を実行すると、下の結果が得られます。

lvchildparentpath
11NULL1
2211-2
3321-2-3
temptable

 この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の書き方の違いで少し悩まされたのは内緒です。

まとめ

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


CSS Database GET HTML MSMM POST SQL SQLServer WordPress 印刷



デル株式会社

ウイルスバスター公式トレンドマイクロ・オンラインショップ

プレミアムバンダイ