SQL Server で再帰的自己結合を使用して階層データを効率的に取得するにはどうすればよいですか?

SQL Server での再帰的自己結合を使用した階層データの効率的な取得

SQL Server の再帰的自己結合は、単一テーブル内の階層データ構造をナビゲートする効果的な方法を提供します。 この手法には、テーブルの行を走査して、階層関係に基づいて関連データを収集することが含まれます。 次のクエリは、合理化されたアプローチを示しています:

<code class="language-sql">WITH q AS (
    SELECT *
    FROM mytable
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q;</code>

このクエリは、「q」という名前の再帰共通テーブル式 (CTE) を利用します。 CTE は、階層内の最上位ノードを表す、ParentID が NULL であるすべての行を選択することから始まります。 その後、mytable と CTE 自体を再帰的に結合し、ParentID 列と PersonID 列に基づいて子行を追加します。この反復プロセスは、すべての子孫が含まれるまで継続されます。

次のサンプルを考えてみましょうmytable:

PersonID	Initials	ParentID
1	CJ	NULL
2	EB	1
3	MB	1
4	SW	2
5	YT	NULL
6	IS	5

CJ の階層を取得すると (PersonID = 1)、次の結果が得られます:

PersonID	Initials	ParentID
1	CJ	NULL
2	EB	1
3	MB	1
4	SW	2

同様に、EB の階層 (PersonID = 2) は次を返します:

PersonID	Initials	ParentID
2	EB	1
4	SW	2

階層順序を維持するには、順序付け条件を組み込んだ変更されたクエリが有益です。

<code class="language-sql">WITH q AS (
    SELECT m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
    FROM mytable m
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*, q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q
ORDER BY bc;</code>

この強化されたクエリでは、CTE に bc 列が導入され、各ノードからルートまでのパスを表します。 bc で結果を順序付けすると、兄弟順序を含む階層構造が確実に保持されます。 ORDER BY 句をカスタマイズして、必要に応じて兄弟の順序を調整できます。

以上がSQL Server で再帰的自己結合を使用して階層データを効率的に取得するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。