セージ の メモ書き

メモこそ命の恩人だ

SQL - 自己結合(再帰結合)

自己結合

SQLの自己結合って何?同じテーブルを結合する方法とは?! | ポテパンスタイル

逆引きSQL構文集 - テーブルを自己結合する(再帰結合)

SQLServerの共通テーブル式(CTE)を使用した再帰SQLでパスなどの階層情報を組み立てる(フォルダパス、パンくず) | 株式会社アースリンク

SELECT 別名1.カラム名, ...
   FROM テーブル名 別名1 INNER JOIN テーブル名 別名2
   ON 別名1.カラム名 = 別名2.カラム名; 
  • 自分自身のテーブルと結合すること。
  • 自身のテーブル内に存在する列を外部キーとして扱う。
  • 同じテーブル名なので、別名を付与して処理する。

以下のテーブルで試してみる。

ID(PK) ParentID(FK) Name
1 NULL Aさん
2 1 Bさん
3 1 Cさん
4 2 Dさん
5 2 Eさん
6 NULL Fさん
7 6 Gさん
  • Aさんトップ。
  • Aさんのこどもに、BさんCさん。
  • Bさんのこどもに、DさんEさん。

内部結合

-- 親子関係が表示できた。
-- B.ParentIdに等しいA.Idを抽出するため、Aが親情報となる。
SELECT A.Name as 親, B.Name asFROM SampleTable as A INNER JOIN SampleTable as B
   ON A.Id = B.ParentId;

-- 親子関係が表示できた。
-- A.ParentIdに等しいB.Idを抽出するため、Bが親情報となる。
SELECT A.Name as 子, B.Name asFROM SampleTable as A INNER JOIN SampleTable as B
   ON A.ParentId = B.Id;


WITH句(再帰結合)

SQL WITH句で自己結合を再帰的に問い合わせる - 追憶行

"親->子" への参照

-- "ID:1" 以下の全要素を参照できた。

WITH TEMP(Id) AS(
    SELECT Id
    FROM [SampleTable]
    WHERE Id = 1
    UNION ALL
    SELECT A.Id
    FROM [SampleTable] A, TEMP B
    WHERE A.ParentId = B.Id
)
SELECT Id FROM TEMP

"子->親" への参照

-- "ID:5" 以上の全要素を参照できた。

WITH TEMP(Id, ParentId) AS(
    SELECT Id, ParentId
    FROM [SampleTable]
    WHERE Id = 5
    UNION ALL
    SELECT A.Id, A.ParentId
    FROM [SampleTable] A, TEMP B
    WHERE A.Id = B.ParentId
)
SELECT Id FROM TEMP

試験問題メモ

  • "親:子 = 1:n" の自己参照の場合

    • 階層構造
  • "親:子 = n:n" の自己参照の場合

    • ネットワーク構造
    • 子より、親の数が多い場合がある。


補足

使用DDL

USE SampleDB

/* テーブル削除 */
IF ObJECt_ID('[SampleTable]') IS NOT NULL DROP TABLE [SampleTable];

/* テーブル追加 */
CREATE TABLE [SampleTable]
(
    [Id] int NOT NULL,
    [ParentId] int,
    [Name] nvarchar(50),
    PRIMARY KEY ([Id])
);

/* 外部キー制約 */
ALTER TABLE [SampleTable]
    ADD FOREIGN KEY ([ParentId])
    REFERENCES [SampleTable] ([Id])
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
;

/* レコード追加 */
INSERT INTO [dbo].[SampleTable]([Id],[ParentId],[Name]) VALUES (1, NULL, 'Aさん');
INSERT INTO [dbo].[SampleTable]([Id],[ParentId],[Name]) VALUES (2, 1, 'Bさん');
INSERT INTO [dbo].[SampleTable]([Id],[ParentId],[Name]) VALUES (3, 1, 'Cさん');
INSERT INTO [dbo].[SampleTable]([Id],[ParentId],[Name]) VALUES (4, 2, 'Dさん');
INSERT INTO [dbo].[SampleTable]([Id],[ParentId],[Name]) VALUES (5, 2, 'Eさん');
INSERT INTO [dbo].[SampleTable]([Id],[ParentId],[Name]) VALUES (6, NULL, 'Fさん');
INSERT INTO [dbo].[SampleTable]([Id],[ParentId],[Name]) VALUES (7, 6, 'Gさん');



以上