mysql8 公用表表达式CTE的使用方法实例分析
本文实例讲述了mysql8公用表表达式CTE的使用方法。分享给大家供大家参考，具体如下：
公用表表达式CTE就是命名的临时结果集，作用范围是当前语句。
说白点你可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。
一、cte的语法格式：
with_clause: WITH[RECURSIVE] cte_name[(col_name[,col_name]...)]AS(subquery) [,cte_name[(col_name[,col_name]...)]AS(subquery)]...
二、哪些地方可以使用with语句创建cte
1、select，update，delete语句的开头
WITH...SELECT... WITH...UPDATE... WITH...DELETE...
2、在子查询的开头或派生表子查询的开头
SELECT...WHEREidIN(WITH...SELECT...)... SELECT*FROM(WITH...SELECT...)ASdt...
3、紧接SELECT，在包含SELECT声明的语句之前
INSERT...WITH...SELECT... REPLACE...WITH...SELECT... CREATETABLE...WITH...SELECT... CREATEVIEW...WITH...SELECT... DECLARECURSOR...WITH...SELECT... EXPLAIN...WITH...SELECT...
三、我们先建个表，准备点数据
CREATETABLE`menu`( `id`int(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'ID', `name`varchar(32)DEFAULT''COMMENT'名称', `url`varchar(255)DEFAULT''COMMENT'url地址', `pid`int(11)DEFAULT'0'COMMENT'父级ID', PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
插入点数据:
INSERTINTO`menu`(`id`,`name`,`url`,`pid`)VALUES('1','后台管理','/manage','0');
INSERTINTO`menu`(`id`,`name`,`url`,`pid`)VALUES('2','用户管理','/manage/user','1');
INSERTINTO`menu`(`id`,`name`,`url`,`pid`)VALUES('3','文章管理','/manage/article','1');
INSERTINTO`menu`(`id`,`name`,`url`,`pid`)VALUES('4','添加用户','/manage/user/add','2');
INSERTINTO`menu`(`id`,`name`,`url`,`pid`)VALUES('5','用户列表','/manage/user/list','2');
INSERTINTO`menu`(`id`,`name`,`url`,`pid`)VALUES('6','添加文章','/manage/article/add','3');
INSERTINTO`menu`(`id`,`name`,`url`,`pid`)VALUES('7','文章列表','/manage/article/list','3');
四、非递归CTE
这里查询每个菜单对应的直接上级名称，通过子查询的方式。
selectm.*,(selectnamefrommenuwhereid=m.pid)aspnamefrommenuasm;
这里换成用cte完成上面的功能
withcteas( select*frommenu ) selectm.*,(selectcte.namefromctewherecte.id=m.pid)aspnamefrommenuasm;
上面的示例并不是很好，只是用来演示cte的使用。你只需要知道cte就是一个可复用的结果集就好了。
相比较某些子查询，cte的效率会更高，因为非递归的cte只会查询一次并复用。
cte可以引用其他cte的结果，比如下面的语句，cte2就引用了cte1中的结果。
withcte1as( select*frommenu ),cte2as( selectm.*,cte1.nameaspnamefrommenuasmleftjoincte1onm.pid=cte1.id ) select*fromcte2;
五、递归CTE
递归cte是一种特殊的cte，其子查询会引用自身，with子句必须以withrecursive开头。
cte递归子查询包括两部分：seed查询和 recursive查询，中间由union[all]或uniondistinct分隔。
seed查询会被执行一次，以创建初始数据子集。
recursive查询会被重复执行以返回数据子集，直到获得完整结果集。当迭代不会生成任何新行时，递归会停止。
withrecursivecte(n)as( select1 unionall selectn+1fromctewheren<10 ) select*fromcte;
上面的语句，会递归显示10行，每行分别显示1-10数字。
递归的过程如下：
1、首先执行select1得到结果1，则当前n的值为1。
2、接着执行selectn+1fromctewheren<10，因为当前n为1，所以where条件成立，生成新行，selectn+1得到结果2，则当前n的值为2。
3、继续执行 selectn+1fromctewheren<10，因为当前n为2，所以where条件成立，生成新行，selectn+1得到结果3，则当前n的值为3。
4、一直递归下去
5、直到当n为10时，where条件不成立，无法生成新行，则递归停止。
对于一些有上下级关系的数据，通过递归cte就可以很好的处理了。
比如我们要查询每个菜单到顶级菜单的路径
withrecursivecteas(
selectid,name,cast('0'aschar(255))aspathfrommenuwherepid=0
unionall
selectmenu.id,menu.name,concat(cte.path,',',cte.id)aspathfrommenuinnerjoincteonmenu.pid=cte.id
)
select*fromcte;
递归的过程如下：
1、首先查询出所有pid=0的菜单数据，并设置path为'0'，此时cte的结果集为pid=0的所有菜单数据。
2、执行menuinnerjoincteonmenu.pid=cte.id，这时表menu与cte(步骤1中获取的结果集)进行内连接，获取菜单父级为顶级菜单的数据。
3、继续执行 menuinnerjoincteonmenu.pid=cte.id，这时表menu与cte(步骤2中获取的结果集)进行内连接，获取菜单父级的父级为顶级菜单的数据。
4、一直递归下去
5、直到没有返回任何行时，递归停止。
查询一个指定菜单所有的父级菜单
withrecursivecteas( selectid,name,pidfrommenuwhereid=7 unionall selectmenu.id,menu.name,menu.pidfrommenuinnerjoincteoncte.pid=menu.id ) select*fromcte;
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。