概念描述

  MySQL8.0开始支持WITH语法，既Common Table Expressions（CTE）可以翻译为公用表表达式，CTE是一个临时结果集，用WITH关键字开头，仅在一个单一的SQL语句执行范围内存在（select/insert/update/delete），这个公用表表达式在不影响原有SQL语句的情况下，允许使用者临时创建一个内存中的结果集，然后对其进行引用和操作。
 

主要用途

  WITH语句或者CTE的主要用途就是创建一个临时性的结果集，该结果集在后面语句中可以进行单次或者多次引用，主要就是用来解决查询复杂语句，因为它可以将一个多次需要的结果集存储下来，方便后续查询可以直接使用，还可以将复杂的SQL语句进行简单化处理，使得代码更加清晰。
 

测试验证

1. 通用表表达式

语法示例1：可以定义一个或者多个通用表达式cte1,cte2，中间用逗号分隔

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;

语法示例3：可以使用在updte 和 delete语句中

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

2. 递归通用表表达式

语法示例：递归公用表表达式是具有引用其自身名称的子查询的表达式

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

执行时，该语句会产生以下结果，即包含简单线性序列的单个列：

说明：
  递归 CTE 子查询有两部分，用 UNION ALL 或 UNION [DISTINCT]

SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets

第一个SELECT生成 CTE 的初始行，但不引用 CTE 名称。
第二个SELECT 生成额外的行并通过引用其FROM子句中的 CTE 名称进行递归。当这部分没有产生新行时，递归结束。因此，递归 CTE 由非递归 SELECT部分和递归SELECT部分组成。

注意：递归SELECT部分不得包含以下结构：

• 聚合函数，例如SUM()
• 窗口函数
• GROUP BY
• ORDER BY
• DISTINCT

知识总结

  通用表达式WITH子句是MySQL8.0中提供的新特性语句，可以为查询提供临时表。这样可以避免在多个查询中重复定义数据，提高代码可读性，提高查询效率。

参考文档

https://dev.mysql.com/doc/refman/8.0/en/with.html