Mysql 简明教程
MySQL - Common Table Expression (WITH)
The MySQL Common Table Expression
MySQL 公共表表达式 (CTE) 是一个临时结果集或表，它存在于单个查询的持续时间内。我们可以使用 CTE 来引用单个 SELECT、UPDATE、INSERT、DELETE、CREATE、VIEW 或 MERGE 语句上下文中结果集。
The MySQL Common Table Expression (CTE) is a temporary result-set or table that exists for the duration of a single query. We can use the CTEs to reference the result-set within the context of a single SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, or MERGE statement.
CTE 的范围仅限于该特定查询。它通过将复杂查询分解为简单的块来简化复杂查询。
The scope of a CTE is limited to that specific query. It makes complex queries simple by breaking them into simple blocks.
The MySQL WITH Clause
MySQL WITH 子句用于通过包含一个或多个逗号分隔的子句创建 CTE。子句提供一个子查询来生成结果集。
The MySQL WITH clause is used to create CTEs by having one or more comma-separated subclauses. The subclauses provide a subquery that generates the result-set.
在 8.0 之前的 MySQL 版本中不能使用 WITH 子句。
WITH clause cannot be used in MySQL versions before 8.0.
Syntax
以下是 MySQL WITH 子句的语法 −
Following is the syntax of the MySQL WITH clause −
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 等详细信息 −
Assume we have created a table named DATA containing details such as id, name, age and salary −
CREATE TABLE DATA(
ID INT,
NAME CHAR(20),
AGE INT,
SALARY INT
);现在，让我们使用 INSERT INTO 语句将值插入到上面创建的表中，如下所示 −
Now, let us insert values into the table created above using the INSERT INTO statement as shown below −
INSERT INTO DATA VALUES
(101, 'John', 25, 55452),
(102, 'Jane', 29, 66458),
(103, 'Arub', 35, 36944);在以下示例中，WITH 子句用于创建一个名为 CTE 的 CTE，然后查询该 CTE 以从 DATA 表中检索数据 −
In the following example, the WITH clause is used to create a CTE named CTE, which is then queried to retrieve the data from the DATA table −
WITH CTE AS
(Select ID, NAME, AGE, SALARY FROM DATA)
SELECT * FROM CTE;在执行上述代码后，我们如下获得输出 −
After executing the above code, we get the output as follows −
CTE from Multiple Tables
我们可以通过使用逗号 (',') 分隔每个 CTE 子句来从多张表创建 CTE。
We can create CTEs from multiple tables by separating each CTE subclause using a comma (',').
Example
假设我们创建了一个名为 EMPLOYEE 的表，并将数据填充到其中，如下所示：
Suppose we have created a table with name EMPLOYEE and populated data into it as shown below −
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 表中 −
Here, we are inserting records into the EMPLOYEE table −
INSERT INTO EMPLOYEE VALUES
(101, 'Serena', 'Williams', 27, 'F', 9000, 101),
(102, 'Virat', 'Kohli', 20, 'M', 6000, 102);获得的 EMPLOYEE 表如下 −
The EMPLOYEE table obtained is as follows −
现在，创建一个名为 CONTACT 的其他表 −
Now, we create another table CONTACT −
CREATE TABLE CONTACT(
ID INT NOT NULL,
EMAIL CHAR(20) NOT NULL,
PHONE LONG,
CITY CHAR(20)
);让我们在 CONTACT 表中插入一些记录 -
Let us insert some records in the CONTACT table −
INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, 'serena@mymail.com', 'Hyderabad'),
(102, 'virat@mymail.com', 'Vishakhapatnam');生成的 CONTACT 表如下 -
The CONTACT table produced is as follows −
以下示例使用名为“exp1”和“exp2”的公共表表达式 (CTE) 从 EMPLOYEE 和 CONTACT 表中分别选择特定列。最终的 SELECT 语句联接这些 CTE，组合每个 CTE 中选择列 -
Following example uses Common Table Expressions (CTEs) named "exp1" and "exp2" to separately select specific columns from the EMPLOYEE and CONTACT tables. The final SELECT statement joins these CTEs, combining the chosen columns from each −
WITH
exp1 AS (SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE),
exp2 AS (SELECT EMAIL, PHONE FROM CONTACT)
SELECT * FROM exp1 JOIN exp2;以下是上面代码的输出： -
Following is the output of the above code −