with as 与临时表的区别_《MySQL 入门教程》第 20 篇 通用表表达式

文章来源：https://blog.csdn.net/horses/article/details/108053464

原文作者：不剪发的Tony老师

来源凭条：CSDN

上一篇介绍了子查询的各种概念和作用，本篇我们讨论一个和派生表类似但更加强大的功能：通用表表达式。

通用表表达式(Common Table Expression)是一个在语句级别定义的临时结果集，定义之后可以在该语句中多次进行引用。MySQL 8.0 开始支持 CTE，包括简单形式的 CTE 和递归形式的 CTE。

20.1 简单 CTE

CTE 也称为 WITH 子句，它的基本语法如下：

WITH cte_name (col1, col2, ...) AS (  subquery) SELECT * FROM cte_name;

其中，WITH 关键字表明这是一个通用表表达式；cte_name 是它的名字，括号内是可选的字段名；AS 之后是它的定义语句；最后在主查询语句中通过名字引用了前面定义的 CTE。

以下语句是上一篇中的派生表示例，用于查找部门信息和对应的员工数量：

select d.dept_name as "部门名称",       coalesce(de.emp_number,0) as "员工数量"from department dleft join (select dept_id,                  count(*) as emp_number           from employee           group by dept_id) deon (d.dept_id = de.dept_id);

我们可以使用 CTE 将其改写如下：

with de(dept_id, emp_number) AS (  select dept_id,         count(*) as emp_number  from employee  group by dept_id)select d.dept_name as "部门名称",       coalesce(de.emp_number,0) as "员工数量"from department dleft join de on (d.dept_id = de.dept_id);

其中，WITH 子句定义了一个临时结果集，名称为 de；AS 关键字指定了 de 的结构和数据，包含了每个部门的编号和员工数量；最后在连接查询的 JOIN 中使用了临时表 de。该语句的结果与上面的示例相同，但是在逻辑上更加清晰。

WITH 子句相当于定义了一个变量表达式，表达式的值是一个表，因此称为通用表表达式。CTE 和子查询类似，可以用于 SELECT、INSERT、UPDATE、DELETE 以及 CREATE VIEW 等语句。

一个语句中可以定义多个 CTE，一个 CTE 被定义之后可以多次引用，而且可以被后面定义的其他 CTE 引用。例如：

with t1(n) as (  select 2),t2(m) as (  select n + 1  from t1)select t1.n, t2.m, t1.n * t2.mfrom t1, t2;n|m|t1.n * t2.m|-|-|-----------|2|3|          6|

以上示例中定义了 2 个 CTE；第一个 CTE 名称为 t1，包含了一条记录；第二个 CTE 名称为 t2，引用 t1 生成了一条记录；每个 CTE 之间使用逗号进行分隔；最后的 SELECT 语句使用前面定义的 2 个 CTE 进行连接查询。

在编程语言中，通常会定义一些变量和函数(方法)；变量可以被重复使用，函数可以将代码模块化并且提高程序的可读性与可维护性。与此类似，MySQL 中的通用表表达式能够简化复杂的连接查询和子查询，同时实现查询结果的重复利用，从而提高复杂查询语句的可读性和性能。

20.2 递归 CTE

简单 CTE 可以将 SQL 语句进行模块化，便于阅读和理解；而递归形式的 CTE 可以对自己进行引用，从而非常方便地遍历具有层次结构或者树状结构的数据，例如组织结构和航班中转信息查询。

例如，以下语句使用递归 CTE 生成了一个 1 到 10 的数字序列：

with recursive t(n) as(  select 1  union all  select n + 1 from t where n < 10)select n from t;n |--| 1| 2| 3| 4| 5| 6| 7| 8| 9|10|

其中，RECURSIVE 关键字表示递归；递归 CTE 包含两部分，UNION ALL 中的第一个查询语句用于生成初始化数据，第二个查询语句引用了 CTE 自身。该语句的执行过程如下：

  1. 运行初始化语句，生成数字 1；
  2. 第 1 次运行递归部分，此时 n 等于 1，满足查询条件 n < 10，返回数字 2( n+1 )；
  3. 第 2 次运行递归部分，此时 n 等于 2，满足查询条件 n < 10，返回数字 3( n+1 )；
  4. 第 9 次运行递归部分，此时 n 等于 9，满足查询条件 n < 10，返回数字 10( n+1 )；
  5. 第 10 次运行递归部分，此时 n 等于 10；由于不满足查询条件 n < 10，不返回任何结果并且结束递归；
  6. 最后的查询语句返回 t 中的全部数据，也就是一个 1 到 10 的数字序列。

递归 CTE 非常合适用于生成具有某种规律的数字序列，例如斐波那契数列(Fibonacci series)，更多示例可以参考这篇文章。

员工表中存储了员工的各种信息，包括员工编号、姓名以及员工经理的编号。公司的老板“刘备”没有上级，对应的经理为空。该公司的组织结构图如下：

0c595f0c646b3cd830d1e54ced169c5d.png

以下语句利用递归 CTE 生成了一个组织结构图，显示每个员工的从上到下的管理路径：

with recursive employee_path (emp_id, emp_name, path) as(  select emp_id, emp_name, cast(emp_name as char(1000)) as path  from employee  where manager is null  union all  select e.emp_id, e.emp_name, cast(concat(ep.path, '->', e.emp_name) as char(1000))  from employee_path ep  join employee e on ep.emp_id = e.manager)select *from employee_pathorder by emp_id;

其中，employee_path 是一个递归 CTE；其中的初始化部分用于查找上级经理为空的员工，也就是公司的老板：

select emp_id, emp_name, cast(emp_name as char(1000)) as pathfrom employeewhere manager is null;emp_id|emp_name|path|------|--------|----|     1|刘备    |刘备 |

“刘备”是公司的老板。然后第一次执行递归部分，将初始化的结果(employee_path)与员工表进行连接查询，找出“刘备”的所有直接下属员工。返回的结果如下：

emp_id|emp_name |path       |------|---------|-----------|     1|刘备      |刘备       |     2|关羽      |刘备->关羽  |     3|张飞      |刘备->张飞  |     4|诸葛亮     |刘备->诸葛亮|     7|孙尚香     |刘备->孙尚香|     9|赵云      |刘备->赵云  |

其中 CONCAT 连接函数用于将之前的管理路径加上当前员工的姓名，生成新的管理路径。不断执行该过程继续返回其他的员工，直到不再返回新的员工为止，最终的返回结果如下：

emp_id|emp_name |path               |------|---------|-------------------|     1|刘备      |刘备               |     2|关羽      |刘备->关羽          |     3|张飞      |刘备->张飞          |     4|诸葛亮     |刘备->诸葛亮        |     5|黄忠      |刘备->诸葛亮->黄忠   |     6|魏延      |刘备->诸葛亮->魏延   |...    25|孙乾      |刘备->关羽->法正->孙乾|

20.3 递归限制

通常来说，递归 CTE 的定义中需要包含一个终止递归的条件；否则的话，递归将会进入死循环。递归终止的条件可以是遍历完表中的所有数据，不再返回结果；或者是一个 WHERE 终止条件。

以下语句删除了上文生成数字序列的示例中的 WHERE 终止条件：

with recursive t(n) as(  select 1  union all  select n + 1 from t -- where n < 10)select n from t;ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

默认情况下，MySQL 递归 1000 次后返回错误。这一次数限制由系统变量 cte_max_recursion_depth 进行控制；如果 CTE 递归的次数超过了该变量的值，服务器将会强制终止语句的执行。

cte_max_recursion_depth 可以在会话级别或者全局级别进行设置。例如：

set session cte_max_recursion_depth = 1000000;set global cte_max_recursion_depth = 1000000;

另外，也可以在 CTE 语句中使用优化器提示：

with recursive t(n) as(  select 1  union all  select n + 1 from t where n < 10000)select /*+ SET_VAR(cte_max_recursion_depth = 1M) */ n from t;

除了递归次数的限制之外，递归 CTE 的递归部分(UNION 之后的 SELECT 语句)不允许出现以下内容：

  • 聚合函数，例如 SUM 等；
  • 窗口函数；
  • GROUP BY；
  • ORDER BY；
  • DISTINCT。

另外，递归部分只能引用 CTE 名称一次，而且只能在 FROM 子句中(不能在子查询中)引用；如果在递归部分使用其他表和 CTE 进行连接查询， CTE 不能出现在 LEFT JOIN 的右侧。

如果想要进一步学习，通用表表达式常见的案例包括地铁换乘线路查询以及社交网络关系分析等。

对了，在这里说一下，我目前是在职Java开发，如果你现在正在学习Java，了解Java，渴望成为一名合格的Java开发工程师，在入门学习Java的过程当中缺乏基础入门的视频教程，可以关注并私信我：01。获取。我这里有最新的Java基础全套视频教程。