MySQL With 句
このチュートリアルでは、共通テーブル式（CTE）とも呼ばれる MySQL の WITH 句について学習します。CTE は、難しいサブクエリデータを操作する場合に使用されます。
また、共通テーブル式（CTE）を使用して、複雑なクエリを読みやすく理解しやすい方法で記述できるようにする方法についても学習します。また、ネストされた WITH 句を使用できるかどうかも確認します。
共通テーブル式は MySQL バージョン 8.0 より前では使用できなかったことに注意してください。使用するには、MySQL バージョン 8.0 以降が必要です。MySQL バージョン 8.0 の新機能はここで確認できます。
MySQL の使用 WITH 句別名共通テーブル式
MySQL の WITH 句を使用するには、最初に CTE を理解しましょう。Common Table Expressions（CTE）は、それが記述されているステートメント内の実行スコープにのみ存在する一時的な結果セットと呼ばれます。
WITH 句を使用すると、メインクエリ内で簡単に使用できる複雑なサブクエリ（SELECT、INSERT、UPDATE、または DELETE）に名前を割り当てることができます。すべてのデータベースが WITH 句をサポートしているわけではないことに注意してください。
同じ WITH 句内で 1つまたは複数のサブクエリと CTE を使用できますが、ネストされた WITH（WITH 句内の別の WITH）を使用することはできません。tb_order という名前のテーブルを作成し、それにデータを入力して、WITH 句を練習してみましょう。
サンプルコード：
# SQL Programming Using MySQL Version 8.27
CREATE TABLE `practice_with_clause`.`tb_order` (
ORDER_ID INTEGER NOT NULL,
CUSTOMER_FIRST_NAME VARCHAR(30) NOT NULL,
CUSTOMER_LAST_NAME VARCHAR(30) NOT NULL,
CITY_NAME VARCHAR(64) NOT NULL,
PURCHASED_PRODUCTS VARCHAR(64) NOT NULL,
ORDER_DATE DATE NOT NULL,
PRIMARY KEY (ORDER_ID)
);
テーブルの下のデータベースで、テーブルが正常に作成されていることを確認してください。
次の INSERT コマンドを使用して、テーブルに 7つのレコードを入力します。
# SQL Programming Using MySQL Version 8.27
INSERT INTO practice_with_clause.tb_order
(ORDER_ID, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, CITY_NAME, PURCHASED_PRODUCTS, ORDER_DATE)
VALUES
(1,'John','Horton', 'Washington', 'Books', '2021-05-03'),
(2,'Banji','Horton', 'Florida', 'Pens', '2010-5-6'),
(3,'Nayya','Sofia', 'South Carolina', 'Books', '2011-10-15'),
(4,'Martell','Daniel', 'Michigan', 'NoteBooks', '2012-12-02'),
(5,'Sana','Preston', 'Michigan', 'White Board Marker', '2013-08-27'),
(6,'Gulraiz','Yonja', 'Washington', 'Books', '2021-05-03'),
(7,'Mashal','Naaz', 'Florida', 'Comic Books', '2019-01-01');
次に、SELECT コマンドを使用してデータを表示します。
# SQL Programming Using MySQL Version 8.27
SELECT * FROM practice_with_clause.tb_order;
この時点で、WITH 句を使用して共通テーブル式を使用し、以下に示すように複雑なサブクエリを操作します。
# SQL Programming Using MySQL Version 8.27
WITH cte_order AS
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category"
FROM cte_order;
上記のクエリをいくつかのセクションに分けて理解しましょう。
共通テーブル式：cte_order
サブクエリ：
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders FROM practice_with_clause.tb_order GROUP BY PURCHASED_PRODUCTS
メインクエリ：
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category" FROM cte_order;
CTE は、データを読み取るためにメインクエリで自分自身を参照していることに注意してください。私のデータによると、次の出力が表示されます（出力は異なる場合があります）。
共通テーブル式実行スコープ
CTE は実行範囲内でのみ機能すると述べたように、どのようにしたらよいでしょうか。次のスクリーンショットを参照してください。
緑のボックスで強調表示されているコードのみを選択すると、cte_order という名前の CTE の実行範囲内に留まりますが、赤のボックス内のコードのみを選択すると、実行範囲外になり、cte_order という名前の共通テーブル式。これは、CTE が記述されているのと同じ WITH 句内で CTE を参照できることを意味します。
複数の共通テーブル式を持つ MySQL WITH 句の使用
複数の共通テーブル式を使用して、WITH 句を練習してみましょう。
WITH
cte_order AS
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
),
cte_location AS
(
SELECT COUNT(CITY_NAME) as City
FROM practice_with_clause.tb_order
WHERE CITY_NAME = 'Washington'
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category", City
FROM cte_order,cte_location;
これで、次の出力が表示されます。
同様に、別の CTE から以前に定義された 1つの共通テーブル式を参照することもできます。両方の共通テーブル式が同じ WITH 句内に記述されていることを確認してください。
まとめ
上記の説明を考慮して、WITH 句は、難しいサブクエリの操作に役立つ共通テーブル式を利用するために使用されると結論付けました。同じ WITH 句内で複数のサブクエリと共通テーブル式を使用できますが、ネストされた WITH 句を含めることはできません。また、異なる WITH 句から CTE を参照することもできません。
