Sql 简明教程
SQL - Common Table Expression (CTE)
公共表表达式 (CTE) 可以使管理和编写复杂查询变得更容易，让它们更具可读性和简单性，就像数据库视图和派生表一样。通过将复杂查询分解为简单的块，我们可以重用或重写查询。
The SQL Common Table Expression
MySQL 中的 WITH 子句用于指定公共表表达式。
SQL 中的公共表表达式 (CTE) 是一个一次性结果集，即它是一个仅在执行一次查询期间存在的临时表。它允许我们特别在该查询中使用数据，例如在 SELECT 、 UPDATE 、 INSERT 、 DELETE 、 CREATE 、 VIEW 或 MERGE 语句中使用数据。
CTE 是临时的，因为无法将其存储在任何地方以备以后使用；一旦执行查询，它就丢失了。
The MySQL WITH Clause
要指定公共表表达式，我们使用 WITH 子句，它由一个或多个逗号分隔的子句组成。在每个子句中，我们可以提供生成结果集的子查询，并为此子查询分配一个名称。
在 8.0 之前的 MySQL 版本中，不能使用 WITH 子句。
Syntax
以下是使用 WITH 子句创建 CTE 的语法 −
WITH CTE_NAME (column_name) AS (query)
SELECT * FROM CTE_NAME;其中，
-
CTE_NAME − 为 CTE 分配的名称。
-
column_name − 这是 CTE 的列名称，可以用来提高查询的可读性。
-
query − 它定义了 CTE，它可以是任何有效的 SQL 查询。
-
定义 CTE 之后，你可以在同一会话中的后续查询中引用它。
Example
假设我们使用 CREATE TABLE 语句在 MySQL 数据库中创建名为 CUSTOMERS 的表，如下所示：
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)
);现在，我们将向上面创建的表插入一些记录：
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 );创建的表如下所示：
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。
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 定义，有效地将它们合并成一条语句。
Syntax
以下是多公用表表达式 (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。
Example
在这里，我们定义了两个 CTE，分别是 ‘CUSTOMERS_IN_DELHI’ 和 ‘CUSTOMERS_IN_MUMBAI’，以根据他们的地址将客户分成德里和孟买。然后，我们使用 UNION ALL 运算符将这两个 CTE 中的结果合并成一个结果集，检索两个城市中的客户信息。
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
公用表表达式是一个查询，它不断循环地引用自己的结果，直到返回一个空结果。
递归查询在其执行期间不断遍历数据的子集，并以自引用方式定义自身。这种自引用机制允许它重复处理和扩展其结果，直到满足停止条件。
要让 CTE 具有递归性，它必须包含一条 UNION ALL 语句，并提供利用 CTE 本身的查询的第二个定义。这允许 CTE 重复引用其自己的结果，在查询中创建递归行为。
Example
现在，我们使用名为 recursive_cust 的递归 CTE 从上面创建的 ‘CUSTOMERS’ 表中检索数据。最初，我们选择薪水高于 3000 的客户，然后使用 UNION ALL 运算符将年龄超过 25 的客户递归追加到结果集中：
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 的数据将递归显示，如下所示：
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 |