[Oracle]WITH句で階層問い合わせを行う

OracleではWITH句を使って階層問い合わせを行うことができます。

サンプルデータ

説明として、階層を持つフォルダを管理するFOLDER_TBLとフォルダに格納するファイルを管理するFILE_TBLを用意します。

CREATE TABLE FOLDER_TBL (
  FOLDER_ID NUMBER(10) NOT NULL,
  PARENT_ID NUMBER(10),
  FOLDER_NAME VARCHAR2(255) NOT NULL,
  PRIMARY KEY (FOLDER_ID),
  FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER_TBL(FOLDER_ID) ON DELETE CASCADE
);

FOLDER_TBLはフォルダを識別するためのFOLDER_IDとあるフォルダとその上位フォルダを紐づけるためのPARENT_IDを持っています。PARENT_IDは最上位フォルダの場合はNULLになります。

CREATE TABLE FILE_TBL (
  FILE_ID NUMBER(10) NOT NULL,
  FOLDER_ID NUMBER(10) NOT NULL,
  FILE_NAME VARCHAR2(255) NOT NULL,
  FILE_SIZE NUMBER(10),
  PRIMARY KEY (FILE_ID),
  FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER_TBL(FOLDER_ID) ON DELETE CASCADE
);

ファイルはFILE_IDによって識別され、格納先のフォルダに紐づけるためのFOLDER_IDを持っています。FOLDER_IDはNULLになることはなく、必ずどこかのフォルダに紐づきます。

-- FOLDER_TBLテーブルにデータを登録するSQL
INSERT INTO FOLDER_TBL (FOLDER_ID, parent_id, FOLDER_NAME) VALUES (1, NULL, 'Folder 1');
INSERT INTO FOLDER_TBL (FOLDER_ID, parent_id, FOLDER_NAME) VALUES (2, 1, 'Folder 1-1');
INSERT INTO FOLDER_TBL (FOLDER_ID, parent_id, FOLDER_NAME) VALUES (3, NULL, 'Folder 2');

-- FILE_TBLテーブルにデータを登録するSQL
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (1, 1, 'file1.txt', 100);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (2, 1, 'file2.txt', 200);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (3, 2, 'file3.txt', 150);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (4, 2, 'file4.txt', 300);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (5, 3, 'file5.txt', 50);
INSERT INTO FILE_TBL (FILE_ID, FOLDER_ID, FILE_NAME, FILE_SIZE) VALUES (6, 3, 'file6.txt', 250);

サンプルデータを用意しました。

FOLDER_TBLは以下のようなデータとなっています。最上位階層は二つ、うち一つにはサブフォルダがあります。

FOLDER_ID	PARENT_ID	FOLDER_NAME
1	NULL	Folder 1
2	1	Folder 1-1
3	NULL	Folder 2

FILE_TBLは以下のようなデータになっています。各フォルダに2ファイルずつファイルが格納されています。

FILE_ID	FOLDER_ID	FILE_NAME	FILE_SIZE
1	1	file1.txt	100
2	1	file2.txt	200
3	2	file3.txt	150
4	2	file4.txt	300
5	3	file5.txt	50
6	3	file6.txt	250

下層から上層に向かって階層問い合わせをする

あるファイルを指定し、その格納フォルダから最上位フォルダまでの階層を取得してみます。ここではサブフォルダにあるfile3.txtが格納されているフォルダから上位フォルダへ向かって階層問い合わせを行います。

WITH FOLDER_HIERARCY (
    FOLDER_ID,
    PARENT_ID,
    FOLDER_NAME,
    FOLDER_PATH
) AS (
  -- 最下層のフォルダ
  SELECT
    FOLDER_ID,
    PARENT_ID,
    FOLDER_NAME,
    FOLDER_NAME FOLDER_PATH
  FROM
    FOLDER_TBL FD1
  WHERE
    -- フォルダに格納されているファイルのファイル名が'file3.txt'
    EXISTS(SELECT 1 FROM FILE_TBL FL1 WHERE FD1.FOLDER_ID = FL1.FOLDER_ID AND FL1.FILE_NAME = 'file3.txt')
  UNION ALL
  SELECT
    FD2.FOLDER_ID,
    FD2.PARENT_ID,
    FD2.FOLDER_NAME,
    FD2.FOLDER_NAME || ' / ' || BASE.FOLDER_PATH
  FROM
    FOLDER_HIERARCY BASE -- このテーブルに対して
    INNER JOIN FOLDER_TBL FD2 -- このテーブルを結合していく
    ON
      BASE.PARENT_ID = FD2.FOLDER_ID -- 下位フォルダのPARENT_IDに一致するFOLDER_IDのフォルダは1つ上位のフォルダとなる
)
SELECT
  FOLDER_PATH || ' / file3.txt'
FROM
  FOLDER_HIERARCY
WHERE
  PARENT_ID IS NULL -- PARENT_IDがNULLのレコードに絞り込むと最上位フォルダまでのパスが得られる
;

ポイントは、

• 階層問い合わせをするときは項目を明示する（しないとエラーになる）
• 基準となるレコードに対して上位のレコードを結合していく

です。下層から上層に向かって階層問い合わせする場合は、基準となるテーブル（BASE）の親IDと一つ上位となるテーブル（FD2）のIDを結合する点に気をつけましょう。

上層から下層に向かって階層問い合わせをする

同じことを逆からやってみましょう。

WITH FOLDER_HIERARCY (
    FOLDER_ID,
    PARENT_ID,
    FOLDER_NAME,
    FOLDER_PATH
) AS (
  -- 最上層のフォルダ
  SELECT
    FOLDER_ID,
    PARENT_ID,
    FOLDER_NAME,
    FOLDER_NAME FOLDER_PATH
  FROM
    FOLDER_TBL FD1
  WHERE
    FD1.PARENT_ID IS NULL
  UNION ALL
  SELECT
    FD2.FOLDER_ID,
    FD2.PARENT_ID,
    FD2.FOLDER_NAME,
    BASE.FOLDER_PATH || ' / ' || FD2.FOLDER_NAME
  FROM
    FOLDER_HIERARCY BASE -- このテーブルに対して
    INNER JOIN FOLDER_TBL FD2 -- このテーブルを結合していく
    ON
      BASE.FOLDER_ID = FD2.PARENT_ID -- 上位フォルダのFOLDER_IDに一致するPARENT_IDのフォルダは1つ下位のフォルダとなる
)
SELECT
  FOLDER_PATH || ' / ' || FILE_NAME
FROM
  FOLDER_HIERARCY T1
  INNER JOIN FILE_TBL T2
  ON
    T1.FOLDER_ID = T2.FOLDER_ID
WHERE
  FILE_NAME = 'file3.txt'
;

上層から下層に向かって階層問い合わせする場合は、基準となるテーブル（BASE）のIDと一つ下位となるテーブル（FD2）の親IDを結合する点に気をつけましょう。

いずれの検索結果もFolder 1 / Folder 1-1 / file3.txtとなります。