介绍
MySQL8.0支持公共表表达式common table expression（CTE）。CTE是临时结果集，存在于单个语句的范围内，也可以在该语句中引用。在很多场景下，可以简化复杂的嵌套查询。
比如下面派生表方式可以转换成CTE方式：
SELECT * FROM (SELECT 1) AS dt; #临时表
↓
WITH cte AS (SELECT 1) SELECT * FROM cte; #CTE方式
CTE表达式是DML语句语法的可选部分，使用WITH关键字子句定义。基本语法如下:
with_clause:
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] .
使用方式
WITH不是单纯的SELECT语句，可以结合其他DML语句。
1.在包含WITH子句的语句中，可以引用多个CTE，每个CTE名称来访问相应的CTE结果集。在生成的结果集中可以进行JOIN操作。
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
2.指定列名方式
如果CTE名称后面有一个带括号的名称列表，则这些名称就是列名。
WITH cte (col1, col2) AS
(
SELECT 1, 2
UNION ALL
SELECT 3, 4
)
SELECT col1, col2 FROM cte;
则，列名来自AS（子查询）部分中第一个SELECT的选择列表：
WITH cte AS
(
SELECT 1 AS col1, 2 AS col2
UNION ALL
SELECT 3, 4
)
SELECT col1, col2 FROM cte;
备注：最好在指定类型，这样就不会存在隐私转换问题。比如CAST，CONVERT函数。
3.在SELECT、UPDATE和DELETE语句的开头使用。
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
4.在子查询里。包括派生表子查询。
SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...
5.其他语句结合 WiTH SELECT方式实现。
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
6.在同一级别上只允许使用一个WITH子句，使用多个WITH语句可以嵌套在子语句中。
WITH cte1 AS (SELECT 1)
SELECT * FROM ( WITH cte2 AS (SELECT 2)
SELECT * FROM cte2 JOIN cte1) AS dt;
7.CTE中支持，支持UNION ALL或UNION DISTINCT语法。
#UNION ALL
WITH cte (col1, col2) AS
(
SELECT 1, 2
UNION ALL
SELECT 1, 2
)
SELECT col1, col2 FROM cte;
#UNION DISTINCT
WITH cte (col1, col2) AS
(
SELECT 1, 2
UNION DISTINCT
SELECT 1, 2
)
SELECT col1, col2 FROM cte;
递归用法
除了上面基础用法之外，递归用法很实用。引用自身，把查询出来的结果再次代入到查询子句中继续查询。
如下示例，通过CTE里WHERE条件里控制递归值：
mysql> WITH RECURSIVE cte (n) AS
(
SELECT 1 as n
UNION ALL
SELECT n + 1 FROM cte WHERE n Table scan on cte (cost=4.19..5.45 rows=2)
-> Materialize recursive CTE cte (cost=2.93..2.93 rows=2)
-> Rows fetched before execution (cost=0..0 rows=1)
-> Repeat until convergence
-> Filter: (cte.n Scan new records on cte (cost=2.73 rows=2) |
+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
影响CTE的最大递归深度参数是cte_max_recursion_depth，默认值是1000。当操作这个阀值，执行停止同时就会报错。如下：
mysql> show variables like 'cte_max_recursion_depth';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| cte_max_recursion_depth | 1000 |
+-------------------------+-------+
1 row in set (0.00 sec)
#设置5代入次数
mysql> set cte_max_recursion_depth=5;
mysql> WITH RECURSIVE cte (n) AS
( SELECT 1 UNION ALL SELECT n + 1 FROM cte where n> WITH RECURSIVE cte (n) AS
(SELECT 1 UNION ALL SELECT n + 1 FROM cte where n CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
mysql> INSERT INTO employees VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),(29, "Pedro", 198),(4610, "Sarah", 29),(72, "Pierre", 29),(123, "Adil", 692);
mysql> WITH RECURSIVE employee_paths (id, name, path) AS
(
SELECT id, name, CAST(id AS CHAR(200))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM employee_paths AS ep JOIN employees AS e
ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;
+------+---------+-----------------+
| id | name | path |
+------+---------+-----------------+
| 333 | Yasmina | 333 |
| 198 | John | 333,198 |
| 29 | Pedro | 333,198,29 |
| 4610 | Sarah | 333,198,29,4610 |
| 72 | Pierre | 333,198,29,72 |
| 692 | Tarek | 333,692 |
| 123 | Adil | 333,692,123 |
+------+---------+-----------------+
7 rows in set (0.00 sec)
递归SELECT部分不能包含以下构造：
- 聚合函数，如SUM（）
- 窗口功能
- GROUP BY
- ORDER BY
- DISTINCT
- 递归SELECT部分必须只引用CTE一次，并且只能在其FROM子句中引用，而不能在任何子查询中引用。
总结
整体来说，在大多数场景下CTE能够简化SQL，实现递归查询，提高可读性，同时也能使用CTE代替部分视图&存储过程的实现，提高整体性能。
当然临时表也能实现，但CTE与派生表临时表相比具有一些优势：
- 派生表在查询中只能被引用一次。CTE可以被多次引用。若要使用派生表结果的多个实例，必须多次派生该结果。
- CTE可以是自引用（递归）。
- 一个CTE可以引用另一个。
- 当CTE的定义出现在语句的开头而不是嵌入其中时，它可能更容易阅读。
- CTE类似于使用CREATE[TEMPORARY]TABLE创建的表，但不需要显式定义或删除。对于CTE使用中，不需要创建表的权限。
