[SQLServer]階層データを再帰的CTEを使って簡単にするテクニック方法カテゴリーやフォルダ

SQL Serverを使ってて、組織図、カテゴリー構造、タスク管理などの階層データ・親子関係データを扱う際には、一般的なSQLクエリでは複雑さと冗長性が問題となりがちです。しかし、Common Table Expressions（CTE）の導入により、これらの課題を解決し、階層データの取得を効率化する方法が提供されています。この記事では、CTEの基礎から応用までを解説し、階層データを簡潔かつ効果的に管理するテクニックを紹介します。

再帰的SQLを使うと、どんなケースが解決出来るの？
実例：カテゴリーとサブカテゴリー
まとめ
1. おすすめ情報

再帰的SQLを使うと、どんなケースが解決出来るの？

組織図の表示
カテゴリーとサブカテゴリーのリストアップ
プロジェクトのタスク管理
フォルダとサブフォルダの構造の表示
掲示板やコメントのスレッド表示
パンくずリストの生成

実例：カテゴリーとサブカテゴリー

よくある例としてカテゴリーとサブカテゴリーの親子関係ですよね。この階層データを扱うためにCTE（Common Table Expressions）を使用する場合、以下のようなステップで具体的な説明を行います。まず、サンプルデータ構造として、カテゴリーを表すテーブルがあると仮定しましょう。

サンプルデータ

テーブル名: Categories

CategoryID	CategoryName	ParentCategoryID
1	Electronics	NULL
2	Computers	1
3	Smartphones	1
4	Laptops	2
5	Tablets	2

このテーブルでは、CategoryIDは各カテゴリーのユニークな識別子、CategoryNameはカテゴリー名、ParentCategoryIDはそのカテゴリーの親カテゴリーのIDを示しています。親カテゴリーがない場合（最上位レベルのカテゴリーの場合）はParentCategoryIDがNULLになります。

SQLクエリ

次のSQLクエリは、CTEを使用して上記の階層構造を持つカテゴリーのリストを取得します。

WITH CategoryHierarchy AS (
    -- ベースケース: 親カテゴリーがNULLの、つまり最上位のカテゴリーを選択
    SELECT CategoryID, CategoryName, ParentCategoryID, CAST(CategoryName AS VARCHAR(MAX)) AS Path
    FROM Categories
    WHERE ParentCategoryID IS NULL
    UNION ALL
    -- 再帰的ケース: 上で選択したカテゴリーを親とするサブカテゴリーを選択し、パスに追加
    SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID, CAST(p.Path + ' -> ' + c.CategoryName AS VARCHAR(MAX)) AS Path
    FROM Categories c
    INNER JOIN CategoryHierarchy p ON c.ParentCategoryID = p.CategoryID
)
SELECT * FROM CategoryHierarchy;

WITH CategoryHierarchy AS (

-- ベースケース: 親カテゴリーがNULLの、つまり最上位のカテゴリーを選択

SELECT CategoryID, CategoryName, ParentCategoryID, CAST(CategoryName AS VARCHAR(MAX)) AS Path

FROM Categories

WHERE ParentCategoryID IS NULL

UNION ALL

-- 再帰的ケース: 上で選択したカテゴリーを親とするサブカテゴリーを選択し、パスに追加

SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID, CAST(p.Path + ' -> ' + c.CategoryName AS VARCHAR(MAX)) AS Path

FROM Categories c

INNER JOIN CategoryHierarchy p ON c.ParentCategoryID = p.CategoryID

)

SELECT * FROM CategoryHierarchy;

SQLクエリの結果

このクエリの結果は、各カテゴリーとそのフルパスを示します。

CategoryID	CategoryName	ParentCategoryID	Path
1	Electronics	NULL	Electronics
2	Computers	1	Electronics -> Computers
3	Smartphones	1	Electronics -> Smartphones
4	Laptops	2	Electronics -> Computers -> Laptops
5	Tablets	2	Electronics -> Computers -> Tablets

この結果から、Path列を使用して各カテゴリーの階層的な位置を一目で確認できます。たとえば、LaptopsカテゴリーはElectronicsの下のComputersカテゴリーに属していることがわかります。

CTEを使うことで、階層構造を持つデータのクエリや、その構造内での各要素の位置関係を効率的に把握できるようになります。特に、カテゴリーとサブカテゴリーのような親子関係を持つデータモデルでは、この手法が大変有効です。

まとめ

CTEを使った階層データの取得は、SQL Serverでのデータ管理を効率化し、複雑なデータ構造を簡単に扱うことを可能にします。組織図、カテゴリー構造、タスク管理など、多層的な情報を扱う際にCTEは大変役立ちます。このテクニックを身につけることで、データベースの潜在能力を最大限に引き出し、より洗練されたデータ操作が可能になります。

おすすめ情報

ある程度SQLに慣れてくるとより複雑なSQLを組むようになり、分析関数（ウインドウ関数）の壁にぶち当たります。本書は私が初級から抜け出すのになったきっかけの本です。比較的新しい関数なので古いSQLの本には載ってません。昔ながらのSQLを書く人はウインドウ関数の理解に苦しむケースが多いと思います。一歩先に進むためにも苦手意識を克服したいですね。