Sql 简明教程
SQL - Common Table Expression (CTE)
公共表表达式 (CTE) 可以使管理和编写复杂查询变得更容易，让它们更具可读性和简单性，就像数据库视图和派生表一样。通过将复杂查询分解为简单的块，我们可以重用或重写查询。
A Common Table Expression (CTE) can make it easier to manage and write complex queries by making them more readable and simple, like database views and derived tables. We can reuse or rewrite the query by breaking down the complex queries into simple blocks.
The SQL Common Table Expression
MySQL 中的 WITH 子句用于指定公共表表达式。
The WITH clause in MySQL is used to specify a Common Table Expression.
SQL 中的公共表表达式 (CTE) 是一个一次性结果集，即它是一个仅在执行一次查询期间存在的临时表。它允许我们特别在该查询中使用数据，例如在 SELECT 、 UPDATE 、 INSERT 、 DELETE 、 CREATE 、 VIEW 或 MERGE 语句中使用数据。
A Common Table Expression (CTE) in SQL is a one-time result set, i.e. it is a temporary table that exists only during the execution of a single query. It allows us to work with data specifically within that query, such as using it in SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, OR MERGE statements.
CTE 是临时的，因为无法将其存储在任何地方以备以后使用；一旦执行查询，它就丢失了。
CTE is temporary because it cannot be stored anywhere for later use; once the query is executed, it is lost.
The MySQL WITH Clause
要指定公共表表达式，我们使用 WITH 子句，它由一个或多个逗号分隔的子句组成。在每个子句中，我们可以提供生成结果集的子查询，并为此子查询分配一个名称。
To specify common table expressions, we use WITH clause that consists of one or more comma-separated subclauses. Within each subclause, we can present a subquery that produces a result set and assigns a name to this subquery.
在 8.0 之前的 MySQL 版本中，不能使用 WITH 子句。
You cannot use the WITH clause in MySQL versions before 8.0.
Syntax
以下是使用 WITH 子句创建 CTE 的语法 −
Following is the syntax to create a CTE using WITH clause −
WITH CTE_NAME (column_name) AS (query)
SELECT * FROM CTE_NAME;其中，
Where,
-
CTE_NAME − It is the name assigned to the CTE.
-
column_name − It is the column names for the CTE, which can be useful for improving query readability.
-
query − It defines the CTE and it can be any valid SQL query.
-
After defining the CTE, you can reference it in subsequent queries within the same session.
Example
假设我们使用 CREATE TABLE 语句在 MySQL 数据库中创建名为 CUSTOMERS 的表，如下所示：
Assume we have created a table named CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，我们将向上面创建的表插入一些记录：
Now, we are inserting some records into the above created table −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );创建的表如下所示：
The table created is as shown below −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
在这里，我们创建一个名为 CUSTOMER_AGE 的公用表表达式 (CTE)，它选择所有年龄为 23 的客户。然后，我们从 CTE 中检索这些客户的 ID、NAME 和 AGE。
Here, we are creating a Common Table Expression (CTE) named CUSTOMER_AGE that selects all customers with an age of 23. We are then retrieving the ID, NAME, and AGE of these customers from the CTE.
WITH CUSTOMER_AGE AS (
SELECT * FROM customers WHERE AGE = 23)
SELECT ID, NAME, AGE FROM CUSTOMER_AGE;CTE from Multiple Tables
我们还可以创建一个公用表表达式 (CTE)，通过 CTE 的子查询中的 JOIN 操作组合多张表中的数据。为此，我们需要使用逗号运算符来分隔每个 CTE 定义，有效地将它们合并成一条语句。
We can also create a Common Table Expression (CTE) that combines data from multiple tables by using JOIN operations within the CTE’s subquery. To do this, we need to use the comma operator to separate each CTE definition, effectively merging them into a single statement.
Syntax
以下是多公用表表达式 (CTE) 的基本语法：
Following is the basic syntax for multiple Common Table Expression (CTE) −
WITH
CTE_NAME1 (column_name) AS (query),
CTE_NAME2 (column_name) AS (query)
SELECT * FROM CTE_NAME1
UNION ALL
SELECT * FROM CTE_NAME2;我们可以将多公用表表达式 (CTE) 与各种 SQL 操作结合使用，例如 UNION、UNION ALL、JOIN、INTERSECT 或 EXCEPT。
We can use multiple Common Table Expressions (CTEs) with various SQL operations, such as UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT.
Example
在这里，我们定义了两个 CTE，分别是 ‘CUSTOMERS_IN_DELHI’ 和 ‘CUSTOMERS_IN_MUMBAI’，以根据他们的地址将客户分成德里和孟买。然后，我们使用 UNION ALL 运算符将这两个 CTE 中的结果合并成一个结果集，检索两个城市中的客户信息。
In here, we are defining two CTEs namely 'CUSTOMERS_IN_DELHI' and 'CUSTOMERS_IN_MUMBAI' to segregate customers based on their addresses in Delhi and Mumbai. Then, we are using the UNION ALL operator to combine the results from both CTEs into a single result set, retrieving customer information from both cities.
WITH
CUSTOMERS_IN_DELHI AS (
SELECT * FROM CUSTOMERS WHERE ADDRESS = 'Delhi'),
CUSTOMERS_IN_MUMBAI AS (
SELECT * FROM CUSTOMERS WHERE ADDRESS = 'Mumbai')
SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_DELHI
UNION ALL
SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_MUMBAI;Recursive CTE
公用表表达式是一个查询，它不断循环地引用自己的结果，直到返回一个空结果。
A common table expression is a query that keeps referring back to its own result in a loop repeatedly until it returns an empty result.
递归查询在其执行期间不断遍历数据的子集，并以自引用方式定义自身。这种自引用机制允许它重复处理和扩展其结果，直到满足停止条件。
A recursive query continually iterates across a subset of the data during its execution, and defines itself in a self-referencing manner. This self-referencing mechanism allows it to repeatedly process and expand its results until a stopping condition is met.
要让 CTE 具有递归性，它必须包含一条 UNION ALL 语句，并提供利用 CTE 本身的查询的第二个定义。这允许 CTE 重复引用其自己的结果，在查询中创建递归行为。
To make a CTE recursive, it must include a UNION ALL statement and provide a second definition of the query that utilizes the CTE itself. This allows the CTE to repeatedly reference to its own results, creating a recursive behaviour in the query.
Example
现在，我们使用名为 recursive_cust 的递归 CTE 从上面创建的 ‘CUSTOMERS’ 表中检索数据。最初，我们选择薪水高于 3000 的客户，然后使用 UNION ALL 运算符将年龄超过 25 的客户递归追加到结果集中：
Now, we are using a recursive CTE named recursive_cust to retrieve data from the 'CUSTOMERS' table created above. Initially, we are selecting customers with salaries above 3000 and then recursively appending customers older than 25 to the result set using the UNION ALL operator −
WITH recursive_cust (ID, NAME, ADDRESS, AGE) AS (
SELECT ID, NAME, ADDRESS, AGE
FROM CUSTOMERS
WHERE SALARY > 3000
UNION ALL
SELECT ID, NAME, ADDRESS, AGE
FROM CUSTOMERS
WHERE AGE > 25
)
SELECT * FROM recursive_cust;Output
执行上述查询时，客户表中所有年龄大于 25 或薪水大于 3000 的数据将递归显示，如下所示：
When the above query is executed, all data from the customers table whose age is greater than 25 or salary is greater than 3000 will be displayed recursively as shown below −
ID |
NAME |
ADDRESS |
AGE |
4 |
Chaitali |
Mumbai |
25 |
5 |
Hardik |
Bhopal |
27 |
6 |
Komal |
Hyderabad |
22 |
7 |
Muffy |
Indore |
24 |
1 |
Ramesh |
Ahmedabad |
32 |
5 |
Hardik |
Bhopal |
27 |
Example
在以下查询中，我们使用名为 Numbers 的递归 CTE 生成并显示 1 到 5 的数字。递归部分不断将 1 添加到前一个值，直到达到 5，从而创建一个序列：
In the following query, we are using a recursive CTE named Numbers to generate and display numbers from 1 to 5. The recursive part continually adds 1 to the previous value until it reaches 5, creating a sequence −
WITH RECURSIVE Numbers AS (
SELECT 1 AS N
UNION ALL
SELECT N + 1 FROM Numbers WHERE N < 5
)
SELECT n FROM Numbers;