再帰CTE（共通テーブル式）で階層構造のパスを作る

検索すると再帰CTEのサンプルはいろいろ見つかりますが、自分で書いたクエリを残しておきたいなと思って書いてみました。おさらいというかメモというか。

上記MSDNにもありますが、再帰CTEのざっくりした書き方はこんな感じです。

with CTE名(カラム1, カラム2, ...)   /* CTEの定義 */
as(
    /* 元となるselect文 */

    union all

    /* CTE名を参照（join）するselect文 */
)
select * from CTE名; /* CTEを使ったselect文など */

初めて見たときに面食らったのを覚えていますが、ポイントはasの中で、

union allの前に、再帰の元になる（ルートになる）select文を書く
union allの後に、with句で指定したCTEとjoinしたselect文を書く

といった感じ。

これだけでは微妙なので、サンプルクエリも書いてみます。

フォルダ名やファイル名を管理するようなテーブル、データがあるとします。

create table FileSys(
    Id int not null,
    Name nvarchar(255) not null,
    ParentId int null,
    IsDirectory bit not null,
    constraint PK_FileSys primary key(Id),
    constraint FK_FileSys foreign key(ParentId) references FileSys(Id)
);

insert into FileSys
values
    (1, N'新しいフォルダ', null, 1),
    (2, N'気持ち新しいフォルダ', 1, 1),
    (3, N'新規 Microsoft Excel ワークシート.xlsx', 2, 0),
    (4, N'古いフォルダ', 1, 1),
    (5, N'もっと古いフォルダ', 4, 1),
    (6, N'新しいテキスト ドキュメント.txt', 4, 0);

こんなツリー構造です。

新しいフォルダ/
├── 気持ち新しいフォルダ/
|   └── 新規 Microsoft Excel ワークシート.xlsx
└── 古いフォルダ/
    ├── もっと古いフォルダ/
    └── 新しいテキスト ドキュメント.txt

このデータでパスを作ってみたいと思います。たとえば、新しいテキストドキュメント.txtの場合は、新しいフォルダ/古いフォルダ/新しいテキストドキュメント.txtといったパスです。

再帰CTEを使って書くと次のようなクエリになります。

with Cte(Id, ParentId, IsDirectory, Path)
as(
    -- ルート（ParentIdがnull）を取得するselect文
    select
        Id,
        ParentId,
        IsDirectory,
        cast((Name + iif(IsDirectory = 1, N'/', N'')) as nvarchar(max))
    from FileSys
    where ParentId is null

    union all

    -- Cteとjoinするselect文
    select
        FileSys.Id,
        FileSys.ParentId,
        FileSys.IsDirectory,
        Cte.Path + Name + iif(FileSys.IsDirectory = 1, N'/', N'')
    from Cte
        inner join FileSys on Cte.Id = FileSys.ParentId
)
select Id, Path
from Cte
order by Path, IsDirectory;

実行すると、

Id  Path
--- --------------------------------------------------------------------------
1   新しいフォルダ/
2   新しいフォルダ/気持ち新しいフォルダ/
3   新しいフォルダ/気持ち新しいフォルダ/新規 Microsoft Excel ワークシート.xlsx
4   新しいフォルダ/古いフォルダ/
5   新しいフォルダ/古いフォルダ/もっと古いフォルダ/
6   新しいフォルダ/古いフォルダ/新しいテキスト ドキュメント.txt

良い感じ。

ここまで書いてふと思いましたが、そもそもCTE（共通テーブル式）って何？という方はこちらが参考になるかと思います。

共通テーブル式の使用