この記事では、階層データを照會するためのSQLの再帰的共通テーブル式（CTE）について説明します。組織チャートの例を使用して構(gòu)造を詳しく説明し、無限の再帰や誤った結(jié)合などの一般的な落とし穴に対処します。オプティ

階層データに再帰CTEを使用します

再帰的な一般的なテーブル式（CTE）は、組織チャート、ファイルシステム、材料紙幣などの階層データを照會するためのSQLの強力なツールです。それらは、その定義內(nèi)でCTE自體を繰り返し參照することにより、あなたが木のような構(gòu)造を橫斷することを可能にします。基本構(gòu)造には、アンカーメンバー（初期クエリ）と再帰メンバー（自己參照部分）が含まれます。

employeesという名前のテーブルで表される組織チャートの簡単な例で説明しましょう。

 <code class="sql">CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255), manager_id INT ); INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1, 'CEO', NULL), (2, 'VP Sales', 1), (3, 'Sales Rep 1', 2), (4, 'Sales Rep 2', 2), (5, 'VP Marketing', 1), (6, 'Marketing Manager', 5);</code>

CEO（Employee_id 1）の下で階層全體を取得するには、再帰CTEを使用します。

 <code class="sql">WITH RECURSIVE EmployeeHierarchy AS ( -- Anchor member: Selects the CEO SELECT employee_id, employee_name, manager_id, 0 as level FROM employees WHERE employee_id = 1 UNION ALL -- Recursive member: Joins with itself to find subordinates SELECT e.employee_id, e.employee_name, e.manager_id, eh.level 1 FROM employees e INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM EmployeeHierarchy;</code>

このクエリはCEOから始まり、従業(yè)員が既に含まれている従業(yè)員に報告しなくなるまで、部下を再帰的に追加します。 level列は、階層の深さを示します。 UNION ALLアンカーと再帰メンバーの結(jié)果を組み合わせています。重要なのは、各従業(yè)員をマネージャーにリンクするemployeesとEmployeeHierarchyとの間の自己結(jié)合と再帰メンバーの自己結(jié)合です。

再帰的なCTEを使用する際に避けるべき一般的な落とし穴

再帰的なCTEを使用すると、いくつかの落とし穴が誤った結(jié)果やパフォーマンスの問題につながる可能性があります。

• 無限の再帰：最も一般的な間違いは、データにサイクルを作成するか、適切な終了條件を持たない再帰クエリを作成することです。これにより、クエリが無期限に実行されます。データが非環(huán)狀であることを確認(rèn)し（従業(yè)員は自分自身に直接または間接的に報告していません）、再帰メンバーが最終的に終了することを確認(rèn)します（たとえば、階層の葉のノードに到達することにより）。
• 誤った結(jié)合條件：再帰メンバーの誤った結(jié)合條件を使用すると、欠落または追加のデータが発生します。結(jié)合條件を注意深く確認(rèn)して、データの階層的な関係を正確に反映していることを確認(rèn)してください。
• 終了條件の欠如：再帰的なCTEには、無限のループを防ぐために明確な終了條件が必要です。これは通常、特定の値（親ID列のNULLなど）をチェックするか、再帰深度を制限することによって行われます。
• データの複製を無視する： UNION ALL UNIONを使用するには、階層に存在する場合は重複する行が含まれます。重複を排除する必要がある場合は、 UNION使用してください。ただし、 UNION ALLすべて一般的に高速です。

大規(guī)模なデータセットの再帰CTEクエリの最適化

再帰的なCTEは、非常に大きな階層データセットで遅くなる可能性があります。いくつかの最適化戦略により、パフォーマンスを改善できます。

• インデックス作成：結(jié)合條件（通常は親子関係列）で使用される列に適切なインデックスが存在するようにします。インデックスは、再帰CTE內(nèi)の結(jié)合を大幅に高速化します。
• フィルタリング：アンカーおよび/または再帰メンバーにWHEREを追加して、階層の不要な枝を除外することにより、再帰の範(fàn)囲を制限します。これにより、処理されたデータの量が減少します。
• 具體化されたビュー：頻繁に実行される再帰クエリについては、階層データを事前にコンピューターする具體化されたビューを作成することを検討してください。これにより、ストレージスペースといくつかのデータのlust性のコストでクエリのパフォーマンスを大幅に改善できます。
• 代替アプローチ：非常に大きなデータセットの場合、特定の階層クエリのパフォーマンスを向上させることができる隣接リストやネストされたセットを使用するなどの代替アプローチを検討してください。再帰的なCTEは、常にすべてのシナリオに最適なソリューションではありません。
• バッチ処理：階層全體を1回のクエリで処理する代わりに、それを小さなバッチに分割することを検討してください。

さまざまなデータベースシステムの再帰CTE

再帰的なCTEは、ほとんどの主要なデータベースシステムでサポートされていますが、構(gòu)文はわずかに異なる場合があります。

• SQL Server： WITH RECURSIVE使用します（ただし、 RECURSIVEキーワードはオプションです）。
• PostgreSQL： WITH RECURSIVE使用します。
• MySQL：バージョン8.0からの再帰CTEをサポートします。構(gòu)文はPostgreSQLに似ています。
• Oracle：最初のCTEをサポートし、 START WITH CONNECT BY 。これは、わずかに異なる構(gòu)文を持っていますが、同じ機能を?qū)g現(xiàn)します。

コア概念は異なるシステムで同じままですが、正しい構(gòu)文とシステム固有の制限または最適化については、特定のデータベースシステムのドキュメントを必ず參照してください。クエリを徹底的にテストし、パフォーマンスをプロファイルしてボトルネックを識別および対処することを忘れないでください。

以上がSQLで再帰CTEを使用して階層データを照會するにはどうすればよいですか？の詳細(xì)內(nèi)容です。詳細(xì)については、PHP 中國語 Web サイトの他の関連記事を參照してください。