SQL Server で再帰的自己結合を使用してカテゴリとその祖先を取得する方法

SQL Server で再帰的自己結合を使用してカテゴリとその祖先を取得する

この記事では、再帰的自己結合を使用して、SQL Server の階層テーブルからカテゴリとその祖先カテゴリを取得する方法を説明します。 このタスクには共通テーブル式 (CTE) を使用します。

シナリオ:

階層カテゴリ構造を表す Categories、Id、および Name 列を含む ParentId テーブルについて考えます。 目標は、特定のカテゴリ (例: 「ビジネス ラップトップ」) とそのすべての親カテゴリを単一のカンマ区切り文字列で返すクエリを作成することです。

再帰的 CTE ソリューション:

このソリューションでは、再帰的 CTE を使用して階層構造を横断します。 クエリは次のとおりです:

<code class="language-sql">WITH CategoryHierarchy AS (
    SELECT id, name, CAST(name AS VARCHAR(MAX)) AS path, parent_id
    FROM Categories
    WHERE parent_id IS NULL  -- Start with root categories
    UNION ALL
    SELECT c.id, c.name, CAST(ch.path + ',' + c.name AS VARCHAR(MAX)), c.parent_id
    FROM Categories c
    INNER JOIN CategoryHierarchy ch ON c.parent_id = ch.id
)</code>

この CTE、CategoryHierarchy は、Categories テーブルをそれ自体に再帰的に結合します。最初の SELECT ステートメントは、ルート カテゴリ (parent_id が NULL であるカテゴリ) を選択します。 UNION ALL は、これを後続の再帰選択と組み合わせ、親カテゴリ名と子カテゴリ名を連結して path 文字列を構築します。

特定のカテゴリと祖先を取得するクエリ:

「ビジネス ラップトップ」カテゴリとその祖先を取得するには:

<code class="language-sql">SELECT id, name, path
FROM CategoryHierarchy
WHERE name = 'Business Laptops';</code>

テーブルとデータの例:

サンプル Categories テーブルを作成し、データを挿入してみましょう:

<code class="language-sql">CREATE TABLE Categories (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    ParentId INT REFERENCES Categories(Id)
);

INSERT INTO Categories (Id, Name, ParentId)
VALUES
(1, 'Electronics', NULL),
(2, 'Laptops', 1),
(3, 'Desktops', 1),
(4, 'Business Laptops', 2),
(5, 'Gaming Laptops', 2);</code>

期待される結果:

「ビジネス ラップトップ」をターゲットとするクエリの場合、予想される出力は次のようになります:

<code>id      name                path
4       Business Laptops     Electronics,Laptops,Business Laptops</code>

このアプローチは、再帰 CTE を使用してカテゴリとその完全な系統を効果的に取得し、SQL Server の階層データをナビゲートするための明確で効率的なソリューションを提供します。 カテゴリ名がこの制限を超える可能性がある場合は、VARCHAR(MAX) の長さを忘れずに調整してください。

以上がSQL Server で再帰的自己結合を使用してカテゴリとその祖先を取得する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。