Mysql 简明教程

MySQL - Common Table Expression (WITH)

The MySQL Common Table Expression

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

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

The MySQL WITH Clause

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

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

Syntax

以下是 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]

Example

假设我们创建了一个名为 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，然后查询该 CTE 以从 DATA 表中检索数据 −

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

在执行上述代码后，我们如下获得输出 −

CTE from Multiple Tables

我们可以通过使用逗号 (',') 分隔每个 CTE 子句来从多张表创建 CTE。

Example

假设我们创建了一个名为 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;

以下是上面代码的输出： -