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, '[email protected]', 'Hyderabad'),
(102, '[email protected]', 'Vishakhapatnam');

生成的 CONTACT 表如下所示：

ID	EMAIL	CITY
101	[email protected]	Hyderabad
102	[email protected]	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	[email protected]	NULL
101	Serena	Williams	[email protected]	NULL
102	Virat	Kohli	[email protected]	NULL
101	Serena	Williams	[email protected]	NULL