MySQL with 子句
在本教程中，我們將學習 MySQL WITH 子句，也稱為 Common Table Expression (CTE)。每當你想要操作困難的子查詢資料時，都會使用 CTE。
我們還將學習 Common Table Expression (CTE) 如何讓你以易於閱讀和理解的方式編寫複雜的查詢。我們還將看看是否可以使用巢狀的 WITH 子句。
請注意，在 MySQL 8.0 版之前，Common Table Expression 不可用。你必須擁有 MySQL 8.0 或更高版本才能使用它。你可以在此處檢視 MySQL 8.0 版中的新功能。
使用 MySQL WITH 子句即 Common Table Expression
要使用 MySQL WITH 子句，讓我們先了解 CTE。公用表表示式 (CTE) 被命名為臨時結果集，僅存在於編寫它的語句的執行範圍內。
通過使用 WITH 子句，你可以為複雜的子查詢指定名稱，你可以在主查詢中輕鬆使用該名稱（SELECT、INSERT、UPDATE 或 DELETE）。請記住，並非所有資料庫都支援 WITH 子句。
你可以在同一個 WITH 子句中使用一個或多個子查詢和 CTE，但不能使用巢狀 WITH（WITH 子句中的另一個 WITH）。讓我們建立一個名為 tb_order 的 Table 並用一些資料填充它以練習 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)
);
確保在 Tables 下的 Database 中成功建立了你的表。
使用下面的 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 的公用表表示式。這意味著你可以在編寫它的同一 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 定義的一個 Common Table Expression。確保兩個公用表表示式都寫在同一個 WITH 子句中。
まとめ
考慮到上面的討論，我們得出結論，WITH 子句用於獲得公共表表示式的優勢，有助於操縱困難的子查詢。我們可以在同一個 WITH 子句中使用多個子查詢和公用表表示式，但不能有巢狀的 WITH 子句。我們也不能從不同的 WITH 子句中引用 CTE。
