SQLServerで共通テーブル式(CTE)を利用して再帰呼び出しする

Microsoft SQL Server 2012ロゴ

SQLServerで、自分のテーブルに紐づくデータを再帰的に呼び出したい場合があります。

その場合は、共通テーブル式(CTE) を利用することで、再帰的にデータを取得できます。

1.再帰的な呼び出し

Webページのパンくずリスト(いまどこにいるかの道標)を考えると想像しやすいかもしれません。

例えば、下記のようなデータの構成の場合です。


トップページ
会社概要
提供サービス
お知らせ
ホームページを公開しました
Facebookページを開設しました
Twitterアカウントを開設しました
Google+ページを開設しました
お問い合わせ
社長ブログ
bashシェルに危険な脆弱性
Windowsアップデート不具合でAsp.NET MVCが使えない
地元のお店開拓-Pizzeria RUBINO-
サイトマップ

これを実現するテーブル「パンくずリストテーブル」は、ページの情報と、親となるページのIDを持った構成となります。

【パンくずリストテーブル】
ID : ページのID (e.g. page-01)
名前 : ページ名称 (e.g. 会社概要)
URL : ページのURL (e.g. http://goodtech.co.jp/company-profile/)
親ID : 親となるID (e.g. page-0)

こういった場合で、「親ID」をたどって全てのページの一覧を取得する方法が、「再帰的な呼び出し」と言います。

2.共通テーブル式(CTE)

SQL Server で、再帰的な呼び出しでデータを取得したい時に利用するのが、共通テーブル式(CTE)です。

共通テーブル式(CTE)は、下記のようなものになります。

  • SQL文を実行した時のみに作成できる一時的なテーブル
  • 自己参照(再帰的な呼び出し)が可能
  • 結果を複数回参照できる

詳細は、Microsoft SQL Server 「共通テーブル式の使用」を参照ください。

3.テストデータの用意

まずは、パンくずリストテーブルを作成します。

-- パンくずリストテーブル作成
CREATE TABLE Breadcrumbs(
    Id varchar(10) NOT NULL,
    Name nvarchar(200) NOT NULL,
    Url nvarchar(1000) NOT NULL,
    ParentId varchar(10),
CONSTRAINT [PK_Breadcrumbs] PRIMARY KEY CLUSTERED
(
    Id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

次に、テストデータを登録します。

一番上のデータとなる「GoodTechトップページ」は、親ID(ParentId)を「NULL」にすることがポイントです。

-- パンくずリストデータ作成
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0', 'GoodTechトップページ', 'http://goodtech.co.jp/', NULL)
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-01', '会社概要', 'http://goodtech.co.jp/company-profile/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-02', '提供サービス', 'http://goodtech.co.jp/services/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-03', 'お知らせ', 'http://goodtech.co.jp/notices/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0301', 'ホームページを公開しました', 'http://goodtech.co.jp/notices/homepage-open/', 'page-03')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0302', 'Facebookページを開設しました', 'http://goodtech.co.jp/notices/facebookpage-opened/', 'page-03')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0303', 'Twitterアカウントを開設しました', 'http://goodtech.co.jp/notices/twitteraccount-opened/', 'page-03')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0304', 'Google+ページを開設しました', 'http://goodtech.co.jp/notices/googlepluspage-opened/', 'page-03')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-04', 'お問い合わせ', 'http://goodtech.co.jp/contact/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-05', '社長ブログ', 'http://goodtech.co.jp/blog/', 'page-0')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0501', '初めてのご挨拶', 'http://goodtech.co.jp/etc/greeting/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0502', 'Windowsのショートカットキーで作業効率アップ', 'http://goodtech.co.jp/work-efficiency/windows-shortcut/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0503', '増税前の駆け込み購入', 'http://goodtech.co.jp/etc/last-minute-demand/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0504', 'モバイルWi-Fiルータとスマホ料金を下げる方法', 'http://goodtech.co.jp/etc/savemoney-smartphone/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0505', '弊社ホームページをリニューアルしました!', 'http://goodtech.co.jp/etc/hp-renewal/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0506', 'bashシェルに危険な脆弱性', 'http://goodtech.co.jp/it/shellsock/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0507', '新米広報担当よりごあいさつ', 'http://goodtech.co.jp/spokesman/spokesman-greetingmessage/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0508', 'BCPについて考える', 'http://goodtech.co.jp/etc/business_continuity_plan/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0509', '箱根日帰り旅', 'http://goodtech.co.jp/spokesman/hakone-travel/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0510', 'Windowsアップデート不具合でAsp.NET MVCが使えない', 'http://goodtech.co.jp/etc/windwos_update-asp_net_mvc-error/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-0511', '地元のお店開拓-Pizzeria RUBINO-', 'http://goodtech.co.jp/spokesman/ayase-rubino/', 'page-05')
INSERT INTO Breadcrumbs (Id, Name, Url, ParentId)VALUES('page-06', 'サイトマップ', 'http://goodtech.co.jp/sitemap/', 'page-0')

データを全件取得すると、下記のようになります。

Microsoft SQL Server 2012でのパンくずリストデータの全件取得結果

4.再帰的なデータの取得

下記SQLで、データを再帰的に取得できます。

-- 共通テーブル式(CTE)を利用して再帰的にデータを取得する。
declare @Id varchar(10) = 'page-0';


WITH Breadcrumbs_CTE AS
(
    SELECT
        b1.*,
        CAST(b1.Id AS nvarchar(4000)) AS IdPath,
        CAST(b1.Name AS nvarchar(4000)) AS NamePath
    FROM
        Breadcrumbs b1
    WHERE
        b1.Id = @Id

    UNION ALL

    SELECT
        b2.*,
        Breadcrumbs_CTE.IdPath + ‘ – ‘ + b2.Id AS IdPath,
        Breadcrumbs_CTE.NamePath + ‘ > ‘ + b2.Name AS NamePath
    FROM
        Breadcrumbs b2
        INNER JOIN Breadcrumbs_CTE
            ON
            b2.ParentId = Breadcrumbs_CTE.Id
)

SELECT
    cte.IdPath,
    cte.NamePath,
    cte.Url,
    cte.Id,
    cte.ParentId
FROM
    Breadcrumbs_CTE cte
ORDER BY
    cte.IdPath

Microsoft SQL Server 2012でのCTEの取得結果

構文は、
WITH [一時テーブル名] AS ( [取得SQL] )
SELECT [列名] FROM [一時テーブル名]
となります。

WITH句のカッコ内で、データを取得します。
その際に、WITH句で指定した一時テーブル名を呼び出すことで、再帰的な呼び出しが可能になります。

WITH句の後のSELECT文は、一時テーブルの内容を表示するための式となります。

いかがでしたか?

試してみると、簡単で便利なものだということがわかります。


グッドテックでは、SQL Server を利用したシステム開発やチューニング、また、Azure SQL Database などについても、多数の実績があります。ご相談は無料で承っておりますので、お気軽にお問い合わせください。


アプリ開発・Web開発のグッドテック(GoodTech) 白井理一朗 がお届けしました。 http://goodtech.co.jp/


皆様のご意見をお待ちしております。