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;

执行上述代码后，我们得到以下输出：

来自多个表的 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 表如下所示：

现在，我们创建另一个表 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 表如下所示：

下面的示例使用名为“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;

以下是上述代码的输出：