再帰的な一般的なテーブル式(CTE)は、組織チャート、ファイルシステム、カテゴリツリーなどの階層データ構造の処理に使用されるSQLの強力なツールです。これらを使用する方法に関する段階的なガイドを次に示します。
アンカーメンバーの定義:再帰CTEの最初の部分はアンカーメンバーで、再帰の出発点を定義します。これは、初期行のセットを返す非再帰クエリです。
<code class="sql">WITH RECURSIVE EmployeeHierarchy AS ( SELECT id, name, manager_id, 0 AS level FROM Employees WHERE manager_id IS NULL -- Start from the top level (eg, CEO)</code>
再帰メンバーを定義します。アンカーメンバーに続いて、再帰メンバーは再帰の進行方法を定義します。 CTE自体を参照して、以前の反復から返された行の上に構築されます。
UNION ALL SELECT e.id, e.name, e.manager_id, level 1 FROM Employees e INNER JOIN EmployeeHierarchy m ON e.manager_id = m.id )
結果を組み合わせてください:再帰的なCTEは、新しい行が生成されるまで自らを構築し続けます。次に、CTEを照会して、目的の結果を取得します。
<code class="sql">SELECT id, name, level FROM EmployeeHierarchy;</code>
この例は、上から始まる従業員の階層を構築し( manager_id
がNULL
)、すべての従業員が含まれるまで各レベルに部下を再帰的に追加します。
再帰CTEの最適化には、パフォーマンスを改善し、リソースの使用量を削減するためのいくつかの戦略が含まれます。
再帰の深さを制限する:再帰の深さに注意してください。可能であれば、最大の深さを制限するためにWHERE
句を実装します。
<code class="sql">WHERE level < 10</code>
Employees
テーブルのIndex manager_id
とid
。再帰CTESを使用する場合、いくつかのタイプのエラーに遭遇する可能性があります。いくつかの一般的な問題とそれらをトラブルシューティングする方法は次のとおりです。
無限ループ: CTEの再帰部分が停止状態なしに自分自身を参照し続けると、無限のループを引き起こす可能性があります。再帰が明確な終了条件を持っていることを確認してください。
<code class="sql">WHERE level < 10</code>
UNION ALL
によって分離されるべきであり、再帰的な参照は再帰メンバーのFROM
にある必要があります。再帰CTEは階層データの処理に強力ですが、特定のユースケースに応じてより適切な代替方法があります。
隣接リストモデル:このモデルは、直接の親子関係を保存します。簡単ですが、階層をナビゲートするために複数のクエリまたはセルフジョインが必要になる場合があります。
<code class="sql">CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(100), manager_id INT, FOREIGN KEY (manager_id) REFERENCES Employees(id) );</code>
具体化されたパス:このモデルは、ルートから各ノードへのパス全体を文字列として保存します。パス全体の迅速な検索に適していますが、頻繁に更新すると複雑になる可能性があります。
<code class="sql">CREATE TABLE Categories ( id INT PRIMARY KEY, name VARCHAR(100), path VARCHAR(1000) );</code>
ネストされたセット:このモデルは、各ノードに左と右の値を割り当てます。これは、親子関係を効率的に決定するために使用できます。階層を迅速に通過する必要があるが、更新するのが難しいクエリには適しています。
<code class="sql">CREATE TABLE Categories ( id INT PRIMARY KEY, name VARCHAR(100), lft INT, rgt INT );</code>
閉鎖テーブル:このモデルは、すべての祖先の子孫の関係を保存し、パスを含むがより多くのストレージスペースが必要なクエリには効率的です。
<code class="sql">CREATE TABLE EmployeeHierarchy ( ancestor INT, descendant INT, PRIMARY KEY (ancestor, descendant), FOREIGN KEY (ancestor) REFERENCES Employees(id), FOREIGN KEY (descendant) REFERENCES Employees(id) );</code>
これらの各モデルには長所と短所があり、選択は、実行する必要があるクエリの種類やデータの頻度など、アプリケーションの特定のニーズに依存します。
以上が階層データにSQLで再帰CTEを使用するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。