MySQL 8.0 WITH クエリの詳細

MySQL 8 の WITH クエリについて学ぶ

序文：

論理的に複雑な SQL の場合、を使用すると一時テーブルの数が大幅に削減され、コードの可読性と保守性が向上します。
MySQL 8.0 ではついに with ステートメントがサポートされました。複雑なクエリの場合、多くの一時テーブルを作成する必要はありません。
公式ドキュメントを見ることができます[クリックしてジャンプ]

1. 例

最初の公式例から、クエリステートメントによって 4 つの一時テーブル ( cte1 、 cte2 、 cte3 、 cte4後者の一時テーブルは、前の一時テーブルのデータに依存します。
最後の行は最終的なクエリ結果です。実際、 ct3結果であるためct4には 3 行のデータが含まれますが、 MAXとMIN 1 行の結果を取得するために使用されます。

cte1(txt) を ("This " を選択) として指定すると、
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1)、
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "素晴らしいクエリ" UNION
                   SELECT "クエリ")、
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
cte4からMAX(txt)、MIN(txt)を選択します。
 
+----------------------------+----------------------+
| 最大(txt) | 最小(txt) |
+----------------------------+----------------------+
| これは素晴らしいクエリです | これは素晴らしいクエリです |
+----------------------------+----------------------+
セット内の1行（0,00秒）

2 番目の公式例は再帰の使用です。ドキュメントを読んで、以下のクエリ結果を分析しました。
まず一時テーブルmy_cteを定義しますmy_cte SELECT 1 AS nを分析すると、一時テーブルの列名はn、値は1であることがわかります。
次に、 SELECT 1+n FROM my_cte WHERE n<10実行します。これは再帰クエリn<10であり、 1+n結果として使用され、一時テーブルにデータが書き込まれます。最後に、 SELECT * FROM my_cteを使用して一時テーブルをクエリするため、クエリ結果は明らかです。

再帰的なmy_cte AS
（
  1 を n として選択
  ユニオンオール
  my_cteから1+nを選択します。n<10
）
my_cte から * を選択します。
 
+------+
| いいえ |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
セット内の 10 行 (0,00 秒)

私の理解によれば、次の 2 つの異なるクエリを作成しましたが、クエリの結果は同じです。
一時テーブル内の複数のクエリ列の数とタイプは同じである必要があることに注意してください。そうでない場合はエラーが報告されます。

これは、最初の行の一時テーブル列名を指定します。WITH RECURSIVE my_cte(a,b,c) AS
（
  1,1,1を選択
  ユニオンオール
  my_cte から 1+a,2+b,3+c を選択、a<10 の場合
）
my_cte から * を選択します。
 
最初の行は列名を指定せず、列名は最初のクエリWITH RECURSIVE my_cte ASによって返された結果によって決定されます。
（
  1 を a として、1 を b として、1 を c として選択します。
  ユニオンオール
  my_cte から 1+a,2+b,3+c を選択、a<10 の場合
）
my_cte から * を選択します。

公式ドキュメントによると、一時テーブルの構文テンプレートは次のようになり、多数の行を持つクエリで構成できます。

WITH RECURSIVE cte_name [列名のリスト] AS
（
  SELECT ... <-- 初期セットを指定する
  ユニオンオール
  SELECT ... <-- 初期セットを指定する
  ユニオンオール
  ...
  SELECT ... <-- 新しい行を導出する方法を指定します
  ユニオンオール
  SELECT ... <-- 新しい行を導出する方法を指定します
  ...
）
[、その他のCTE定義はいくつでも]

公式ドキュメントには、一時テーブルを使用する場合、新しいテーブルを追加、削除、変更、およびクエリできることも記載されています。詳細については、公式ドキュメントをお読みください。

3. 練習する

再帰演習は主に親ノードIDなどを含むテーブルに使用されます。詳細については以下の演習を参照してください。
各地域（州、市、地区）のID、名前、PIDを格納するために次のテーブルを定義します。

 
テーブルtbを作成します(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(64));
 
INSERT INTO tb VALUES('002', 0, '浙江省');
INSERT INTO tb VALUES('001', 0, '広東省');
INSERT INTO tb VALUES('003', '002', '衢州市');
tb VALUES('004', '002', '杭州') に挿入します。
INSERT INTO tb VALUES('005', '002', '湖州市');
INSERT INTO tb VALUES('006', '002', '嘉興市');
INSERT INTO tb VALUES('007', '002', '寧波市');
INSERT INTO tb VALUES('008', '002', '紹興市');
INSERT INTO tb VALUES('009', '002', '泰州市');
INSERT INTO tb VALUES('010', '002', '温州市');
INSERT INTO tb VALUES('011', '002', '麗水市');
INSERT INTO tb VALUES('012', '002', '金華市');
INSERT INTO tb VALUES('013', '002', '舟山市');
tb VALUES('014', '004', 'Uptown') に挿入します。
tb VALUES('015', '004', 'Downtown') に挿入します。
INSERT INTO tb VALUES('016', '004', '拱壽区');
INSERT INTO tb VALUES('017', '004', '余杭区');
INSERT INTO tb VALUES('018', '011', 'Jindong District');
tb VALUES('019', '001', '広州') に挿入します。
INSERT INTO tb VALUES('020', '001', '深セン市');
 
再帰的な cte AS （
 tb から id、name を選択、id='002' を選択
 ユニオンオール
 SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid
) cte から * を選択します。

実行結果:

分析結果には、最初の行SELECT id,name FROM tb WHERE id='002'のデータが含まれます。この時点では、テーブルには 1 行のデータしかありません。次に、テーブルを結合し、 SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pidクエリします。親ノードのデータは再帰的に一時テーブルに格納され、最終的なクエリは再帰結果です。

これで、MySQL WITH クエリの詳細に関するこの記事は終了です。より関連性の高い MySQL WITH クエリの内容については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: