mysql cte 语法,mysql8 公用表表达式CTE的使用方法实例分析

本文实例讲述了mysql8 公用表表达式cte的使用方法。分享给大家供大家参考，具体如下：

公用表表达式cte就是命名的临时结果集，作用范围是当前语句。

说白点你可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，cte可以引用其他cte，但子查询不能引用其他子查询。

一、cte的语法格式：

二、哪些地方可以使用with语句创建cte

1、select， update，delete 语句的开头

2、在子查询的开头或派生表子查询的开头

3、紧接select，在包含 select声明的语句之前

三、我们先建个表，准备点数据

插入点数据:

四、非递归cte

这里查询每个菜单对应的直接上级名称，通过子查询的方式。

这里换成用cte完成上面的功能

上面的示例并不是很好，只是用来演示cte的使用。你只需要知道 cte 就是一个可复用的结果集就好了。

相比较某些子查询，cte 的效率会更高，因为非递归的 cte 只会查询一次并复用。

cte 可以引用其他 cte 的结果，比如下面的语句，cte2 就引用了 cte1 中的结果。

五、递归cte

递归cte是一种特殊的cte，其子查询会引用自身，with子句必须以 with recursive 开头。

cte递归子查询包括两部分：seed 查询 和 recursive 查询，中间由union [all] 或 union distinct 分隔。

seed 查询会被执行一次，以创建初始数据子集。

recursive 查询会被重复执行以返回数据子集，直到获得完整结果集。当迭代不会生成任何新行时，递归会停止。

上面的语句，会递归显示10行，每行分别显示1-10数字。

递归的过程如下：

1、首先执行 select 1 得到结果 1， 则当前 n 的值为 1。

2、接着执行 select n + 1 from cte where n < 10，因为当前 n 为 1，所以where条件成立，生成新行，select n + 1 得到结果 2，则当前 n 的值为 2。

3、继续执行 select n + 1 from cte where n < 10，因为当前 n 为 2，所以where条件成立，生成新行，select n + 1 得到结果 3，则当前 n 的值为 3。

4、一直递归下去

5、直到当 n 为 10 时，where条件不成立，无法生成新行，则递归停止。

对于一些有上下级关系的数据，通过递归cte就可以很好的处理了。

比如我们要查询每个菜单到顶级菜单的路径

递归的过程如下：

1、首先查询出所有 pid = 0 的菜单数据，并设置path 为 '0'，此时cte的结果集为 pid = 0 的所有菜单数据。

2、执行 menu inner join cte on menu.pid = cte.id ，这时表 menu 与 cte (步骤1中获取的结果集) 进行内连接，获取菜单父级为顶级菜单的数据。

3、继续执行 menu inner join cte on menu.pid = cte.id，这时表 menu 与 cte (步骤2中获取的结果集) 进行内连接，获取菜单父级的父级为顶级菜单的数据。

4、一直递归下去

5、直到没有返回任何行时，递归停止。

查询一个指定菜单所有的父级菜单

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://www.cnblogs.com/jkko123/p/10176323.html