MySQL：CTE 通用表达式

CTE（Common Table Expression，通用表达式） 是在 SQL 查询中定义临时结果集的一种方法，它可以让查询更加简洁、可读性更高，并且可以重复引用。

CTE 是在WITH关键字后面定义的一个子查询，可以在同一查询中被引用多次。它可以被认为是一个临时表，只在定义它的查询（例如：SELECT、INSERT、DELECT、UPDATE）中可见，并且这个临时表的生命周期仅限于当前查询，不作为对象被存储。

不同于派生表（Derived Table）CTE可以是自引用的（递归CTE），也可以在
同一查询中多次引用。此外，与派生表相比，CTE提供了更好的可读性和性能。

语法格式如下：

WITH cte_name [column_list] AS(
	-- CTE query
	SELECT column1,column2,...
	FROM table_name
	WHERE condition
	...;
)
SELECT columnlist FROM cte_name;
1
2
3
4
5
6
7
8

• CTE query 中的字段数必须与column_list的字段数相同，若省略column_list，CTE将会使用 CTE query 中的columns

• 还可以使用两个或以上CTE作为临时表进行查询

• CTE 查询定义部分使用 SELECT 语句定义了结果集的内容，可以包含各种查询操作和条件。它的结果集将成为 CTE 的数据。

• 在主查询部分，可以引用 CTE 的名称来访问 CTE 查询部分定义的结果集。

CTE 优点

• 简化查询逻辑：CTE 允许在查询中使用递归、复杂的子查询或多个操作步骤，以更清晰、易读的方式组织查询逻辑。

• 重用和可读性：在同一个查询中多次引用 CTE，避免了重复的子查询，提高了查询的可读性和重用性。

• 性能优化：某些情况下，使用 CTE 可以帮助数据库管理系统进行优化，减少重复查询的开销。

WITH 关键字

WITH 是在 SQL 查询中使用的关键字，用于定义一个或多个通用表达式（Common Table Expression，CTE）。

WITH … INSERT…

在传统的 INSERT 语句中，需要分别指定目标表和要插入的值，而使用 WITH INSERT 技术，可以 先使用 WITH 子句定义一组数据，然后在 INSERT 语句中引用这个临时的数据集合进行插入。基本模式如下：

WITH cte_name [column_list] AS(
	initial_query
	[UNION ALL
	recursive_query]
	...
)
INSERT INTO table_name (column_list)
SELECT column_list FROM cte_name
WHERE condition;
1
2
3
4
5
6
7
8
9

CTE 查询定义部分使用 SELECT 语句定义要插入的数据集合，可以使用 UNION ALL 连接多个 SELECT 语句来构建数据。

最后，在 INSERT INTO 语句中，可以引用定义好的 CTE 数据集合，并将其插入到目标表中。

WITH INSERT 技术并非所有数据库管理系统都支持，具体支持与否要参考所使用数据库的文档。

WITH… UPDATE…

传统的 UPDATE 语句需要指定目标表、要更新的列以及更新的值，使用 WITH UPDATE 技术，可以 先使用 WITH 子句定义一个临时的查询结果集，然后在 UPDATE 语句中引用该结果集进行更新:

WITH cte_name AS(
	query
)
UPDATE table_name
SET column1 = cte_name.column1,
	column2 = cte_name.column2,
	...
FROM cte_name
WHERE table_name.primary_key = cte_name.primary_key;
1
2
3
4
5
6
7
8
9

在 UPDATE 语句中，可以使用 FROM 子句引用定义好的 CTE，将其与目标表进行联结，并使用 CTE 中的列来更新目标表中对应的列。

WITH UPDATE 技术并非所有数据库管理系统都支持，具体支持与否要参考所使用数据库的文档。

WITH… DELETE…

传统的 DELETE 语句需要指定目标表以及删除条件，使用 WITH DELETE 技术，可以 先使用 WITH 子句定义一个临时的查询结果集，然后在 DELETE 语句中引用该结果集进行删除。

WITH cte_name AS(
	query
)
DELETE FROM table_name
WHERE table_name.primary_key IN (SELECT primary_key FROM cte_name);
1
2
3
4
5

在 DELETE 语句中，可以使用 WHERE 子句配合子查询使用定义好的 CTE，以指定要删除的数据的条件。

WITH DELETE 技术并非所有数据库管理系统都支持，具体支持与否要参考所使用数据库的文档。

WITH in Subqueries

WITH 子句还可以在子查询或派生表子查询中使用：

SELECT column_list FROM table1
WHERE column1 IN (WITH cte_name AS (query));

SELECT * 
FROM (WITH cte_name AS (query)) AS derived_table;
1
2
3
4
5

其他应用

WITH 子句还可以用在所有包含 SELECT 子句的语句中，位于SELECT之前：

CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
1
2
3
4
5
6

Recursive CTE

CTE 支持递归，recursive CTE 是具有引用 CTE自身 的 子查询 的 CTE，使用RECURSIVE 关键字进行声明：

WITH RECURSIVE cte_name AS(
	initial_query  -- anchor member
	UNION [ALL | DISTINCT]
	recursive_query  -- recursive member that 
					 --                  references to the CTE name
)
SELECT * FROM cte_name;
1
2
3
4
5
6
7

递归 CTE 的组成部分

递归CTE包含三个部分：

1. initial query：形成CTE结构的基本结果集。初始查询部分被称为锚成员（anchor member）。

2. recursive query：引用CTE名称的查询，通过UNION ALL或UNION DISTINCT操作符与锚成员连接。

3. termination condition：确保在递归成员不返回任何行时停止递归

'''示例'''
WITH RECURSIVE cte_count (n) 
AS (
	-- anchor member
      SELECT 1  
      UNION ALL
    -- recursive member
	  SELECT n + 1 
      FROM cte_count 
    -- termination condition
      WHERE n < 3
    )
SELECT n 
FROM cte_count;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

递归 CTE 的执行顺序

1. 将成员分成两个：anchor member 和 recursive member。

2. 执行锚成员以形成基本结果集(R0)，并将此基本结果集用于下一次迭代。

3. 以 Ri 结果集作为输入执行递归成员，并将 Ri+1 作为输出。

4. 重复第三步，直到递归成员返回空结果集（满足终止条件）。

5. 使用 UNION ALL 运算符组合从 R0 到 Rn 的结果集。

递归成员限制

recursive member 禁止包含：

• 聚合函数：MAX()，MIN()，SUM()，AVG()，COUNT() 等

• GROUP BY 子句

• ORDER BY 子句

• LIMIT 子句

• DISTINCT 子句