MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.15 WITH (共通テーブル式)

version 8.0

9.1 English
9.0 English
8.4 English
8.0 English

MySQL 8.0 リファレンスマニュアル / ... / WITH (共通テーブル式)

このページは機械翻訳したものです。

13.2.15 WITH (共通テーブル式)

共通テーブル式 (CTE) は、単一ステートメントのスコープ内に存在し、あとでそのステートメント内で複数回参照できる名前付き一時結果セットです。次の説明では、CTE を使用するステートメントを記述する方法について説明します。

CTE 最適化の詳細は、セクション8.2.2.4「マージまたは実体化を使用した導出テーブル、ビュー参照および共通テーブル式の最適化」を参照してください。

追加のリソース

次の記事には、多くの例を含む、MySQL での CTE の使用に関する追加情報が含まれています:

共通テーブル式

共通テーブル式を指定するには、カンマ区切りの副次句を持つ WITH 句を使用します。各副次句は、結果セットを生成し、名前をサブクエリーに関連付けるサブクエリーを提供します。次の例では、WITH 句で cte1 および cte2 という CTE を定義し、WITH 句に続く最上位 SELECT で CTE を参照します:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

WITH 句を含むステートメントでは、各 CTE 名を参照して、対応する CTE 結果セットにアクセスできます。

CTE 名は他の CTE で参照できるため、CTE を他の CTE に基づいて定義できます。

CTE は、それ自体を参照して再帰 CTE を定義できます。再帰 CTE の一般的なアプリケーションには、階層データまたはツリー構造化データのシリーズ生成およびトラバースが含まれます。

共通テーブル式は、DML ステートメントの構文のオプション部分です。これらは、WITH 句を使用して定義されます:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name は、単一の共通テーブル式に名前を付け、WITH 句を含むステートメントでテーブル参照として使用できます。

AS (subquery) の subquery 部分は「「CTE のサブクエリー」」と呼ばれ、CTE 結果セットを生成します。 AS の後にカッコが必要です。

サブクエリーが独自の名前を参照する場合、共通テーブル式は再帰的です。 WITH 句の CTE が再帰的な場合は、RECURSIVE キーワードを含める必要があります。詳細は、再帰的な共通テーブル式を参照してください。

特定の CTE のカラム名の決定は、次のように行われます:

CTE 名の後にカッコで囲まれた名前のリストがある場合、それらの名前はカラム名になります:
```
WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;
```
リスト内の名前の数は、結果セット内のカラムの数と同じである必要があります。
それ以外の場合、カラム名は AS (subquery) 部分内の最初の SELECT の選択リストから取得されます:
```
WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;
```

WITH 句は、次のコンテキストで使用できます:

SELECT、UPDATE および DELETE ステートメントの先頭。
```
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
```

サブクエリーの開始時 (導出テーブルサブクエリーを含む):

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

SELECT ステートメントを含むステートメントについては、SELECT の直前を参照してください:

INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

同じレベルで許可される WITH 句は 1 つのみです。 WITH の後に同じレベルの WITH が続くことは許可されていないため、これは不正です:

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

ステートメントを有効にするには、副次句をカンマで区切る単一の WITH 句を使用します:

WITH cte1 AS (...), cte2 AS (...) SELECT ...

ただし、異なるレベルで発生する場合は、ステートメントに複数の WITH 句を含めることができます:

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

WITH 句では、複数の共通テーブル式を定義できますが、各 CTE 名は句に対して一意である必要があります。これは不正です:

WITH cte1 AS (...), cte1 AS (...) SELECT ...

ステートメントを有効にするには、CTE を一意の名前で定義します:

WITH cte1 AS (...), cte2 AS (...) SELECT ...

CTE は、自身または他の CTE を参照できます:

自己参照 CTE は再帰的です。
CTE は、同じ WITH 句で以前に定義された CTE を参照できますが、後で定義された CTE は参照できません。

この制約は、cte1 が cte2 を参照し、cte2 が cte1 を参照する相互再帰 CTE を除外します。これらの参照のいずれかは、後で定義する CTE への参照である必要がありますが、これは許可されていません。
特定のクエリーブロック内の CTE は、より外側のレベルのクエリーブロックで定義された CTE を参照できますが、より内側のレベルのクエリーブロックで定義された CTE は参照できません。

同じ名前のオブジェクトへの参照を解決するには、導出テーブルで CTE を非表示にし、CTE で実テーブル、TEMPORARY テーブルおよびビューを非表示にします。名前解決は、同じクエリーブロック内のオブジェクトを検索し、その名前のオブジェクトが見つからないときに外部ブロックに順に進むことで行われます。

導出テーブルと同様に、CTE には MySQL 8.0.14 より前の外部参照を含めることはできません。これは、SQL 標準の制限ではなく、MySQL 8.0.14 で削除された MySQL 制限です。再帰 CTE に固有の構文上のその他の考慮事項については、再帰的な共通テーブル式を参照してください。

再帰的な共通テーブル式

再帰的共通テーブル式は、独自の名前を参照するサブクエリーを持つ式です。例:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

このステートメントを実行すると、単純な線形順序を含む単一のカラムである次の結果が生成されます:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

再帰 CTE の構造は次のとおりです:

WITH 句内の CTE がそれ自体を参照する場合、WITH 句は WITH RECURSIVE で始まる必要があります。 (CTE がそれ自体を参照していない場合、RECURSIVE は許可されますが必須ではありません。)

再帰 CTE の RECURSIVE を忘れた場合、次のエラーが発生する可能性があります:
```
ERROR 1146 (42S02): Table 'cte_name' doesn't exist
```
再帰 CTE サブクエリーには、UNION [ALL]または UNION DISTINCT で区切られた次の 2 つの部分があります:
```
SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets
```
最初の SELECT は CTE の最初の行を生成し、CTE 名を参照しません。 2 番目の SELECT では、FROM 句で CTE 名を参照することで追加の行が生成され、繰り返されます。再帰は、この部分で新しい行が生成されない場合に終了します。したがって、再帰 CTE は非再帰的 SELECT 部分とそれに続く再帰的 SELECT 部分で構成されます。

各 SELECT 部分自体を複数の SELECT ステートメントの和集合にすることができます。
CTE 結果カラムの型は、非再帰的 SELECT 部分のカラム型からのみ推測され、カラムはすべて NULL 値可能です。型の決定では、再帰的 SELECT 部分は無視されます。
非再帰的部分と再帰的部分が UNION DISTINCT によって分離されている場合、重複する行は削除されます。これは、無限ループを回避するために推移的な閉鎖を実行するクエリーに役立ちます。
再帰部分の各反復は、前の反復によって生成された行に対してのみ動作します。再帰部分に複数のクエリーブロックがある場合、各クエリーブロックの反復は未指定の順序でスケジュールされ、各クエリーブロックは、前の反復または前の反復終了以降に他のクエリーブロックによって生成された行に対して動作します。

前述の再帰 CTE サブクエリーには、最初の行セットを生成するために単一行を取得する次の非再帰部分があります:

SELECT 1

CTE サブクエリーには、次の再帰的部分もあります:

SELECT n + 1 FROM cte WHERE n < 5

反復のたびに、その SELECT は、前の行セットの n の値より大きい新しい値を持つ行を生成します。最初の反復は初期行セット (1) で動作し、1+1=2 を生成します。2 番目の反復は最初の反復行セット (2) で動作し、2+1=3 などを生成します。これは、n が 5 未満になったときに発生する再帰が終了するまで続きます。

CTE の再帰的部分で非再帰的部分よりもカラムの値が広い場合は、データの切捨てを回避するために、非再帰的部分のカラムを拡張する必要がある場合があります。次のステートメントがあるとします。

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

非厳密 SQL モードでは、このステートメントは次の出力を生成します:

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

非再帰的 SELECT によってカラム幅が決定されるため、str のカラム値はすべて'abc'です。したがって、再帰的 SELECT によって生成されるより広い str 値は切り捨てられます。

厳密な SQL モードでは、このステートメントはエラーを生成します:

ERROR 1406 (22001): Data too long for column 'str' at row 1

この問題に対処して、ステートメントで切捨てやエラーが発生しないようにするには、非再帰的 SELECT で CAST() を使用して str カラムの幅を広げます:

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

これで、ステートメントは切捨てなしで次の結果を生成します:

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

カラムには、位置ではなく名前でアクセスします。つまり、次の CTE に示すように、再帰部分のカラムは、別の位置を持つ非再帰部分のカラムにアクセスできます:

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

一方の行の p は前の行の q から導出されるため、正の値と負の値は出力の後続の各行の位置を入れ替えます:

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

再帰的 CTE サブクエリー内では、いくつかの構文制約が適用されます:

再帰的 SELECT 部分には、次の構成を含めることはできません:
- SUM() などの集計関数
- ウィンドウ関数
- GROUP BY
- ORDER BY
- DISTINCT
MySQL 8.0.19 より前は、再帰 CTE の再帰的 SELECT 部分で LIMIT 句を使用することもできませんでした。この制限は MySQL 8.0.19 で解除され、このような場合、オプションの OFFSET 句とともに LIMIT がサポートされるようになりました。結果セットへの影響は、最も外側の SELECT で LIMIT を使用する場合と同じですが、再帰的 SELECT とともに使用すると、要求された数の行が生成されるとすぐに行の生成が停止するため、より効率的です。

これらの制約は、再帰 CTE の非再帰的 SELECT 部分には適用されません。 DISTINCT での禁止は、UNION メンバーにのみ適用されます。UNION DISTINCT は許可されます。
再帰的 SELECT 部分は CTE を参照する必要があるのは、サブクエリーではなく、その FROM 句でのみです。 CTE 以外のテーブルを参照して CTE と結合できます。このような結合で CTE を使用する場合、CTE を LEFT JOIN の右側に配置しないでください。

これらの制約は、MySQL 固有除外である ORDER BY、LIMIT (MySQL 8.0.18 以前) および DISTINCT 以外は、SQL 標準から取得されます。

再帰 CTE の場合、再帰的 SELECT 部品の EXPLAIN 出力行の Extra カラムに Recursive が表示されます。

EXPLAIN によって表示される原価見積は反復当たりの原価を表しますが、これは合計原価と大きく異なる場合があります。オプティマイザは、WHERE 句が false になる時点では予測できないため、反復数を予測できません。

CTE の実際のコストは、結果セットのサイズの影響を受ける場合もあります。多くの行を生成する CTE では、メモリー内からディスク上の形式に変換するために十分な大きさの内部一時テーブルが必要になる場合があり、パフォーマンスが低下する可能性があります。その場合、許可されるインメモリー一時テーブルサイズを増やすと、パフォーマンスが向上する可能性があります。セクション8.4.4「MySQL での内部一時テーブルの使用」を参照してください。

共通テーブル式の再帰の制限

再帰的 CTE では、再帰的 SELECT 部分に再帰を終了する条件が含まれていることが重要です。ランナウェイ再帰 CTE から保護する開発手法として、実行時間に制限を設定することで強制的に終了できます:

cte_max_recursion_depth システム変数は、CTE の再帰レベル数に制限を強制します。サーバーは、この変数の値よりも多くのレベルを繰り返す CTE の実行を終了します。
max_execution_time システム変数は、現在のセッション内で実行される SELECT ステートメントの実行タイムアウトを強制します。
MAX_EXECUTION_TIME オプティマイザヒントは、表示されている SELECT ステートメントに対してクエリーごとの実行タイムアウトを強制します。

再帰 CTE が誤って書き込まれ、再帰実行の終了条件がないとします:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

デフォルトでは、cte_max_recursion_depth の値は 1000 で、CTE は 1000 レベルを超えて繰り返されたときに終了します。アプリケーションでは、セッション値を変更して要件に合せて調整できます:

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

グローバル cte_max_recursion_depth 値を設定して、後で開始するすべてのセッションに影響を与えることもできます。

実行が遅く再帰するクエリー、または cte_max_recursion_depth 値を非常に高く設定する理由があるコンテキストでは、深い再帰から保護する別の方法は、セッションタイムアウトを設定することです。これを行うには、CTE ステートメントを実行する前に、次のようなステートメントを実行します:

SET max_execution_time = 1000; -- impose one second timeout

または、CTE ステートメント自体にオプティマイザヒントを含めます:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

MySQL 8.0.19 以降では、再帰的クエリー内で LIMIT を使用して、最も外側の SELECT に返される行の最大数を設定することもできます。次に例を示します:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

これは、時間制限に加えて、または時間制限を設定するかわりに行うことができます。したがって、次の CTE は、10 千行を返した後、または 1 千秒先に実行された後に終了します:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

実行時間制限のない再帰的クエリーが無限ループに入った場合は、KILL QUERY を使用して別のセッションから再帰的クエリーを終了できます。セッション自体では、クエリーの実行に使用されるクライアントプログラムによってクエリーを強制終了する方法が提供される場合があります。たとえば、mysql では、Control+C と入力すると現在のステートメントが中断されます。

再帰的な共通テーブル式の例

前述のように、再帰的共通テーブル式 (CTE) は、系列の生成および階層データまたはツリー構造化データのトラバースに頻繁に使用されます。このセクションでは、これらの手法の簡単な例をいくつか示します。

フィボナッチシリーズ世代

Fibonacci シリーズは、2 つの数値 0 と 1 (または 1 と 1) で始まり、その後の各数値は前の 2 つの数値の合計です。再帰的共通テーブル式では、再帰的 SELECT によって生成された各行がシリーズの前の 2 つの数値にアクセスできる場合、Fibonacci シリーズを生成できます。次の CTE は、最初の 2 つの番号として 0 と 1 を使用して 10-number シリーズを生成します:

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

CTE は次の結果を生成します:

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

CTE の機能:

n は、行に n 番目の Fibonacci 番号が含まれていることを示す表示カラムです。たとえば、8 番目の Fibonacci 番号は 13 です。
fib_n カラムには、Fibonacci 番号 n が表示されます。
next_fib_n カラムには、数値 n の後の次の Fibonacci 番号が表示されます。このカラムは次の行に次の系カラム値を提供するため、行の fib_n カラムに前の 2 つの系カラム値の合計を生成できます。
再帰は、n が 10 に達すると終了します。これは任意の選択肢で、出力を小さな行セットに制限します。

前述の出力は CTE の結果全体を示しています。その一部のみを選択するには、トップレベルの SELECT に適切な WHERE 句を追加します。たとえば、8 番目のフィボナッチ番号を選択するには、次のようにします:

mysql> WITH RECURSIVE fibonacci ...
       ...
       SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
|    13 |
+-------+

日付シリーズ生成

共通テーブル式では、一連の連続した日付を生成できます。これは、サマリーデータにテーブルされていない日付を含む、シリーズ内のすべての日付の行を含むサマリーを生成する場合に役立ちます。

売上番号のテーブルに次の行が含まれているとします:

mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date       | price  |
+------------+--------+
| 2017-01-03 | 100.00 |
| 2017-01-03 | 200.00 |
| 2017-01-06 |  50.00 |
| 2017-01-08 |  10.00 |
| 2017-01-08 |  20.00 |
| 2017-01-08 | 150.00 |
| 2017-01-10 |   5.00 |
+------------+--------+

このクエリーでは、日当たりの売上が集計されます:

mysql> SELECT date, SUM(price) AS sum_price
       FROM sales
       GROUP BY date
       ORDER BY date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-06 |     50.00 |
| 2017-01-08 |    180.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

ただし、その結果には、テーブルに含まれる日付の範囲でテーブルされない日付の「「穴」」が含まれます。範囲内のすべての日付を表す結果を再帰 CTE を使用して生成し、その日付セットを生成して、LEFT JOIN と販売データを結合できます。

日付範囲シリーズを生成する CTE は次のとおりです:

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;

CTE は次の結果を生成します:

+------------+
| date       |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+

CTE の機能:

非再帰的 SELECT では、sales テーブルにまたがる日付範囲内で最も低い日付が生成されます。
再帰的 SELECT によって生成された各行では、前の行によって生成された日付に 1 日が追加されます。
再帰は、日付が sales テーブルの範囲内の最も高い日付に達した後に終了します。

CTE を sales テーブルに対して LEFT JOIN と結合すると、範囲内の各日付の行を含む売上サマリーが生成されます:

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;

出力は次のようになります:

+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-04 |      0.00 |
| 2017-01-05 |      0.00 |
| 2017-01-06 |     50.00 |
| 2017-01-07 |      0.00 |
| 2017-01-08 |    180.00 |
| 2017-01-09 |      0.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

次の点に注意してください:

クエリーは非効率的ですか。特に、再帰的 SELECT の各行に対して MAX() サブクエリーが実行されているクエリーですか。 EXPLAIN では、MAX() を含むサブクエリーが一度のみ評価され、結果がキャッシュされることが示されます。
COALESCE() を使用すると、sales テーブルに売上データが発生しない日に、sum_price カラムに NULL が表示されなくなります。

階層データトラバース

再帰的な共通テーブル式は、階層を形成するデータを横断する場合に役立ちます。会社の各従業員について、従業員名、ID 番号および従業員マネージャの ID を表示する小さなデータセットを作成する次のステートメントについて考えてみます。最上位レベルの従業員 (CEO) のマネージャ ID は NULL (マネージャなし) です。

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

結果のデータセットは次のようになります:

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

各従業員の管理チェーン (つまり、CEO から従業員へのパス) を含む組織図を作成するには、再帰 CTE を使用します:

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

CTE は次の出力を生成します:

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

CTE の機能:

非再帰的 SELECT は、CEO (NULL マネージャ ID を持つ行) の行を生成します。

path カラムは、再帰的 SELECT によって生成されるより長い path 値のための領域が確保されるように、CHAR(200) に広がります。
再帰的 SELECT によって生成された各行では、前の行によって生成された従業員に直接レポートするすべての従業員が検索されます。このような従業員ごとに、行には従業員 ID と従業員名、および従業員管理チェーンが含まれます。チェーンは、従業員 ID が最後に追加されたマネージャチェーンです。
再帰は、従業員に他の部下がいない場合に終了します。

特定の従業員のパスを検索するには、最上位の SELECT に WHERE 句を追加します。たとえば、Tarek および Sarah の結果を表示するには、次のように SELECT を変更します:

mysql> WITH RECURSIVE ...
       ...
       SELECT * FROM employees_extended
       WHERE id IN (692, 4610)
       ORDER BY path;
+------+-------+-----------------+
| id   | name  | path            |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
|  692 | Tarek | 333,692         |
+------+-------+-----------------+

類似の構成と比較した共通テーブル式

共通テーブル式 (CTE) は、いくつかの点で派生テーブルに似ています:

両方の構成メンバーに名前が付けられます。
両方の構成は、単一のステートメントの有効範囲に存在します。

これらの類似性のため、CTE と導出テーブルは同じ意味で使用できます。簡単な例として、次のステートメントは同等です:

WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;

ただし、CTE には導出テーブルと比較していくつかの利点があります:

導出テーブルは、クエリー内で一度のみ参照できます。 CTE は複数回参照できます。導出テーブルの結果の複数のインスタンスを使用するには、結果を複数回導出する必要があります。
CTE は自己参照 (再帰的) にすることができます。
CTE は別の CTE を参照できます。
CTE 内に埋め込まれるのではなく、CTE の定義がステートメントの先頭に表示されると、CTE が読みやすくなる場合があります。

CTE は、CREATE [TEMPORARY] TABLE で作成されるテーブルに似ていますが、明示的に定義または削除する必要はありません。 CTE の場合、テーブルを作成する権限は必要ありません。

PREV HOME UP NEXT