mysql8 公用表表示式CTE的使用方法例項分析
本文例項講述了mysql8 公用表表示式cte的使用方法。分享給大家供大家參考,具體如下:
公用表表示式cte就是命名的臨時結果集,作用範圍是當前語句。
說白點你可以理解成一個可以複用的子查詢,當然跟子查詢還是有點區別的,cte可以引用其他cte,但子查詢不能引用其他子查詢。
一、cte的語法格式:
1
2
3
4 |
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 語句的開頭
1
2
3 |
with ... select ...
with ... update ...
with ... delete ... |
2、在子查詢的開頭或派生表子查詢的開頭
1
2 |
select ... where id in (with ... select ...) ...
select * from (with ... select ...) as dt ... |
3、緊接select,在包含 select宣告的語句之前
1
2
3
4
5
6 |
insert ... with ... select ...
replace ... with ... select ...
create table ... with ... select ...
create view ... with ... select ...
declare cursor ... with ... select ...
explain ... with ... select ... |
三、我們先建個表,準備點資料
1
2
3
4
5
6
7 |
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; |
插入點資料:
1
2
3
4
5
6
7 |
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
這裡查詢每個選單對應的直接上級名稱,通過子查詢的方式。
1 |
select m.*, (select name from menu where id = m.pid) as pname from menu asm; |
這裡換成用cte完成上面的功能
1
2
3
4 |
with cte as (
select * from menu
)
select m.*, (select cte.name from cte where cte.id = m.pid) as pname frommenu as m; |
上面的示例並不是很好,只是用來演示cte的使用。你只需要知道 cte 就是一個可複用的結果集就好了。
相比較某些子查詢,cte 的效率會更高,因為非遞迴的 cte 只會查詢一次並複用。
cte 可以引用其他 cte 的結果,比如下面的語句,cte2 就引用了 cte1 中的結果。
1
2
3
4
5
6 |
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 查詢會被執行一次,以建立初始資料子集。
recursive 查詢會被重複執行以返回資料子集,直到獲得完整結果集。當迭代不會生成任何新行時,遞迴會停止。
1
2
3
4
5
6 |
with recursive cte(n) as (
select 1
union all
select n + 1 from cte where n < 10
)
select * from cte; |
上面的語句,會遞迴顯示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
2
3
4
5
6 |
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 menuinner join cte on menu.pid = cte.id
)
select * from 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、直到沒有返回任何行時,遞迴停止。
查詢一個指定選單所有的父級選單
1
2
3
4
5
6 |
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資料庫計有所幫助。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69957453/viewspace-2748779/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql with as 公用表表示式(CTE)MySql
- 儲存過程——公用表表示式(CTE)儲存過程
- MYSQL: 表表示式(CTE)實現遞迴例項MySql遞迴
- 使用公用表表示式(CTE)WITH AS提高sql效能,with as【未完待續】SQL
- Sql Server系列:通用表表示式CTESQLServer
- 正規表示式例項蒐集,通過例項來學習正規表示式。
- java正規表示式例項Java
- MongoDB正規表示式匹配使用方法舉例MongoDB
- Java 正規表示式例項操作Java
- js正規表示式例項(整理)JS
- 正規表示式子表示式程式碼例項
- PHP preg match正規表示式函式的操作例項PHP函式
- javascript常用的正規表示式程式碼例項JavaScript
- vim的正規表示式(二)應用例項
- JS常用的匹配正規表示式和例項JS
- 正規表示式分組例項詳解
- 匹配中文正規表示式程式碼例項
- 匹配漢字的正規表示式程式碼例項
- 匹配<a>連結的正規表示式程式碼例項
- 匹配數字的正規表示式程式碼例項
- jQuery鏈式呼叫例項分析jQuery
- PostgreSQL的最佳特性(公用表表示式/WITH語句),你用了嗎?SQL
- 檢驗漢字的正規表示式程式碼例項
- Java經典例項:正規表示式,找到匹配的文字Java
- 可以提取ip地址的正規表示式程式碼例項
- 匹配url連結的正規表示式程式碼例項
- JavaScript正規表示式備忘單附例項JavaScript
- 解析url地址正規表示式程式碼例項
- 正規表示式獲取cookie程式碼例項Cookie
- 正規表示式刪除空格程式碼例項
- 正規表示式匹配空行程式碼例項行程
- python 正規表示式過濾例項1Python
- 通過js正規表示式例項學習正規表示式基本語法JS
- 匹配雙位元組字元的正規表示式程式碼例項字元
- 匹配有效數字的正規表示式程式碼例項
- Java經典例項:正規表示式,替換匹配的文字Java
- 可以提取連結<a>元素的正規表示式程式碼例項
- 可以提取圖片地址的正規表示式程式碼例項