MySQL - 公共表表達式 (WITH)

---

---

MySQL 公共表表達式

MySQL 公共表表達式 (CTE) 是一個臨時結果集或表，它在一個查詢的持續時間記憶體在。我們可以使用 CTE 在單個 SELECT、UPDATE、INSERT、DELETE、CREATE、VIEW 或 MERGE 語句的上下文中引用結果集。

CTE 的範圍僅限於該特定查詢。它透過將複雜查詢分解為簡單的塊來簡化複雜查詢。

MySQL WITH 子句

MySQL WITH 子句用於透過包含一個或多個逗號分隔的子句來建立 CTE。子句提供生成結果集的子查詢。

在 8.0 之前的 MySQL 版本中不能使用 WITH 子句。

語法

以下是 MySQL WITH 子句的語法：

WITH name_for_summary_data AS (SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions <=> (
   SELECT column
   FROM name_for_summary_data
)
[ORDER BY columns]

示例

假設我們建立了一個名為 DATA 的表，其中包含 id、name、age 和 salary 等詳細資訊：

CREATE TABLE DATA(
   ID INT,
   NAME CHAR(20),
   AGE INT,
   SALARY INT
);

現在，讓我們使用 INSERT INTO 語句將值插入到上面建立的表中，如下所示：

INSERT INTO DATA VALUES 
(101, 'John', 25, 55452),
(102, 'Jane', 29, 66458),
(103, 'Arub', 35, 36944);

在下面的示例中，WITH 子句用於建立一個名為 CTE 的 CTE，然後查詢它以從 DATA 表中檢索資料：

WITH CTE AS 
(Select ID, NAME, AGE, SALARY FROM DATA)
SELECT * FROM CTE;

執行上述程式碼後，我們得到以下輸出：

ID	NAME	AGE	SALARY
101	John	25	55452
102	Jane	29	66458
103	Arub	35	36944

來自多個表的 CTE

我們可以透過使用逗號 (',') 分隔每個 CTE 子句來從多個表建立 CTE。

示例

假設我們建立了一個名為 EMPLOYEE 的表，並向其中填充了資料，如下所示：

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);

這裡，我們正在將記錄插入到 EMPLOYEE 表中：

INSERT INTO EMPLOYEE VALUES
(101, 'Serena', 'Williams', 27, 'F', 9000, 101),
(102, 'Virat', 'Kohli', 20, 'M', 6000, 102);

獲得的 EMPLOYEE 表如下所示：

ID	FIRST_NAME	LAST_NAME	AGE	SEX	INCOME	CONTACT
101	Serena	Williams	27	F	9000	101
102	Virat	Kohli	20	M	6000	102

現在，我們建立另一個表 CONTACT：

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

讓我們在 CONTACT 表中插入一些記錄：

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, 'serena@mymail.com', 'Hyderabad'),
(102, 'virat@mymail.com', 'Vishakhapatnam');

生成的 CONTACT 表如下所示：

ID	EMAIL	CITY
101	serena@mymail.com	Hyderabad
102	virat@mymail.com	Vishakhapatnam

下面的示例使用名為“exp1”和“exp2”的公共表表達式 (CTE) 分別從 EMPLOYEE 和 CONTACT 表中選擇特定列。最後的 SELECT 語句連線這些 CTE，組合每個 CTE 中選擇的列：

WITH
exp1 AS (SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE),
exp2 AS (SELECT EMAIL, PHONE FROM CONTACT)
SELECT * FROM exp1 JOIN exp2;

以下是上述程式碼的輸出：

ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE
102	Virat	Kohli	serena@mymail.com	NULL
101	Serena	Williams	serena@mymail.com	NULL
102	Virat	Kohli	virat@mymail.com	NULL
101	Serena	Williams	virat@mymail.com	NULL