MySQL 再帰的 CTE をマスターする:階層の横断とシリーズの生成

再帰 CTE (共通テーブル式) は、独自の名前を使用してそれ自体を参照するサブクエリです。 WITH RECURSIVE を使用して定義されます そして終了条件が必要です。再帰的 CTE は、系列の生成、階層データの走査、グラフの走査に役立ちます。

構文

WITH RECURSIVE cte_name (col1, col2, ...) AS (
 -- Non-recursive (base case): initial rows
 SELECT col1, col2 FROM table_name
 UNION ALL
 -- Recursive case: references cte_name
 SELECT col1, col2 FROM cte_name WHERE condition
)
SELECT * FROM cte_name;

• 最初の SELECT これは、最初の行を提供する基本ケースです。
• UNION ALL 各反復から行を追加します (DISTINCT) 重複を削除します)。
• 2 番目の SELECT WHERE 条件が失敗するまで再帰的なケースが実行されます。

例 1:最初の 5 つの奇数を生成する

WITH RECURSIVE odd_no (sr_no, n) AS (
 SELECT 1, 1
 UNION ALL
 SELECT sr_no + 1, n + 2
 FROM odd_no
 WHERE sr_no < 5
)
SELECT * FROM odd_no;

+-------+---+
| sr_no | n |
+-------+---+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
+-------+---+

基本ケースは (1, 1) を返します。各反復では、sr_no が 1 ずつ増加し、n が 2 ずつ増加します。sr_no が 5 に達すると再帰は停止します。

例 2:従業員階層

マネージャーと従業員の階層を横断する、より実際的な使用法はありますか?

-- Assume: employees(id, name, manager_id)
WITH RECURSIVE org_chart (id, name, level) AS (
 -- Base: top-level manager (no manager)
 SELECT id, name, 0
 FROM employees
 WHERE manager_id IS NULL
 UNION ALL
 -- Recursive: find direct reports
 SELECT e.id, e.name, oc.level + 1
 FROM employees e
 JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

これにより、トップレベルのマネージャー (レベル 0) から始まる組織ツリーが構築され、各レベルのすべてのレポートが再帰的に検索されます。

重要なポイント

• 無限ループを避けるために、再帰 SELECT の WHERE 句には必ず終了条件を含めてください。
• MySQL のデフォルトの再帰制限は 1000 反復です (cte_max_recursion_depth で設定可能) ).
• UNION ALL を使用します パフォーマンスのため。 UNION DISTINCT 重複を削除する必要がある場合のみ

結論

再帰的 CTE は WITH RECURSIVE を使用します 基本ケースと再帰ケースを使用して自己参照クエリを定義します。これらは、MySQL での階層データの走査 (組織図、カテゴリ ツリー)、シリーズの生成、およびグラフの走査に不可欠です。