本文实例讲述了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数据库计有所帮助。