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 ... WHERE id IN (WITH ... SELECT ...) ...SELECT * FROM (WITH ... SELECT ...) AS dt ...

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

INSERT ... WITH ... SELECT ...REPLACE ... WITH ... SELECT ...CREATE TABLE ... WITH ... SELECT ...CREATE VIEW ... WITH ... SELECT ...DECLARE CURSOR ... WITH ... SELECT ...EXPLAIN ... WITH ... SELECT ...

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

CREATE TABLE `menu` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(32) DEFAULT '' COMMENT '名称', `url` varchar(255) DEFAULT '' COMMENT 'url地址', `pid` int(11) DEFAULT '0' COMMENT '父级ID', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入点数据:

INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('1', '后台管理', '/manage', '0');INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('2', '用户管理', '/manage/user', '1');INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('3', '文章管理', '/manage/article', '1');INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('4', '添加用户', '/manage/user/add', '2');INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('5', '用户列表', '/manage/user/list', '2');INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('6', '添加文章', '/manage/article/add', '3');INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('7', '文章列表', '/manage/article/list', '3');

四、非递归CTE

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

select m.*, (select name from menu where id = m.pid) as pname from menu as m;

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

with cte as ( select * from menu) select m.*, (select cte.name from cte where cte.id = m.pid) as pname from menu as m;

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