MySQL - 通用表表达式 (WITH)

• MySQL 通用表表达式
• MySQL WITH 子句
• 来自多个表的通用表表达式 (CTE)

MySQL 通用表表达式

MySQL 通用表表达式 (CTE) 是一个临时结果集或表，在单个查询期间存在。我们可以在单个 SELECT、UPDATE、INSERT、DELETE、CREATE、VIEW 或 MERGE 语句的上下文中使用 CTE 引用结果集。

CTE 的作用域仅限于该特定查询。它通过将复杂的查询分解为简单的块来简化查询。

MySQL WITH 子句

MySQL WITH 子句用于通过包含一个或多个逗号分隔的子句来创建 CTE。这些子句提供生成结果集的子查询。

WITH 子句在 MySQL 8.0 之前的版本中无法使用。

语法

以下是 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、姓名、年龄和薪水等详细信息 -

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，然后查询该 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，将每个表中选定的列组合起来 -

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

以下是上述代码的输出 -