本文實例講述了mysql8 公用表表達式CTE的使用方法。分享給大家供大家參考，具體如下：
公用表表達式CTE就是命名的臨時結(jié)果集，作用范圍是當前語句。
說白點你可以理解成一個可以復用的子查詢，當然跟子查詢還是有點區(qū)別的，CTE可以引用其他CTE，但子查詢不能引用其他子查詢。
一、cte的語法格式：
with_clause:
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
二、哪些地方可以使用with語句創(chuàng)建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 ...
三、我們先建個表，準備點數(shù)據(jù)
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;
插入點數(shù)據(jù):
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 就是一個可復用的結(jié)果集就好了。
相比較某些子查詢，cte 的效率會更高，因為非遞歸的 cte 只會查詢一次并復用。
cte 可以引用其他 cte 的結(jié)果，比如下面的語句，cte2 就引用了 cte1 中的結(jié)果。
with cte1 as (
select * from menu
), cte2 as (
select m.*, cte1.name as pname from menu as m left join cte1 on m.pid = cte1.id
)
select * from cte2;
五、遞歸CTE
遞歸cte是一種特殊的cte，其子查詢會引用自身，with子句必須以 with recursive 開頭。
cte遞歸子查詢包括兩部分：seed 查詢 和 recursive 查詢，中間由union [all] 或 union distinct 分隔。
seed 查詢會被執(zhí)行一次，以創(chuàng)建初始數(shù)據(jù)子集。
recursive 查詢會被重復執(zhí)行以返回數(shù)據(jù)子集，直到獲得完整結(jié)果集。當?shù)粫扇魏涡滦袝r，遞歸會停止。
with recursive cte(n) as (
select 1
union all
select n + 1 from cte where n 10
)
select * from cte;
上面的語句，會遞歸顯示10行，每行分別顯示1-10數(shù)字。
遞歸的過程如下：
1、首先執(zhí)行 select 1 得到結(jié)果 1， 則當前 n 的值為 1。
2、接著執(zhí)行 select n + 1 from cte where n 10，因為當前 n 為 1，所以where條件成立，生成新行，select n + 1 得到結(jié)果 2，則當前 n 的值為 2。
3、繼續(xù)執(zhí)行 select n + 1 from cte where n 10，因為當前 n 為 2，所以where條件成立，生成新行，select n + 1 得到結(jié)果 3，則當前 n 的值為 3。
4、一直遞歸下去
5、直到當 n 為 10 時，where條件不成立，無法生成新行，則遞歸停止。
對于一些有上下級關系的數(shù)據(jù)，通過遞歸cte就可以很好的處理了。
比如我們要查詢每個菜單到頂級菜單的路徑
with recursive cte as (
select id, name, cast('0' as char(255)) as path from menu where pid = 0
union all
select menu.id, menu.name, concat(cte.path, ',', cte.id) as path from menu inner join cte on menu.pid = cte.id
)
select * from cte;
遞歸的過程如下：
1、首先查詢出所有 pid = 0 的菜單數(shù)據(jù)，并設置path 為 '0'，此時cte的結(jié)果集為 pid = 0 的所有菜單數(shù)據(jù)。
2、執(zhí)行 menu inner join cte on menu.pid = cte.id ，這時表 menu 與 cte (步驟1中獲取的結(jié)果集) 進行內(nèi)連接，獲取菜單父級為頂級菜單的數(shù)據(jù)。
3、繼續(xù)執(zhí)行 menu inner join cte on menu.pid = cte.id，這時表 menu 與 cte (步驟2中獲取的結(jié)果集) 進行內(nèi)連接，獲取菜單父級的父級為頂級菜單的數(shù)據(jù)。
4、一直遞歸下去
5、直到?jīng)]有返回任何行時，遞歸停止。
查詢一個指定菜單所有的父級菜單
with recursive cte as (
select id, name, pid from menu where id = 7
union all
select menu.id, menu.name, menu.pid from menu inner join cte on cte.pid = menu.id
)
select * from cte;
更多關于MySQL相關內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關技巧匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。
您可能感興趣的文章:- MySQL8新特性：自增主鍵的持久化詳解
- 詳解MySQL8.0+常用命令
- MySQL8.0新特性之支持原子DDL語句
- MySQL8.0 DDL原子性特性及實現(xiàn)原理
- Mysql8.0使用窗口函數(shù)解決排序問題
- 詳解MySQL 8.0.18命令
- MySQL 8.0.18給數(shù)據(jù)庫添加用戶和賦權問題
- MySql8.0以上版本正確修改ROOT密碼的方法
- 關于SQL中CTE（公用表表達式）（Common Table Expression）的總結(jié)
- Sql學習第三天——SQL 關于CTE(公用表達式)的遞歸查詢使用
- Sql學習第二天——SQL DML與CTE概述
- SqlServer使用公用表表達式（CTE）實現(xiàn)無限級樹形構(gòu)建
