關(guān)于MySQL8的WITH查詢學(xué)習(xí)

前言：

對(duì)于邏輯復(fù)雜的sql，with可以大大減少臨時(shí)表的數(shù)量，提升代碼的可讀性、可維護(hù)性
MySQL 8.0終于開始支持with語句了，對(duì)于復(fù)雜查詢，可以不用寫那么多的臨時(shí)表了。
可以查看官方文檔【點(diǎn)擊跳轉(zhuǎn)】

1、示例

官方第一個(gè)示例，可以看出該查詢語句創(chuàng)建了cte1,cte2,cte3,cte4這4個(gè)臨時(shí)表，后面的臨時(shí)表依賴前面的臨時(shí)表數(shù)據(jù)。
最后一行為最終查詢結(jié)果，實(shí)際ct4因?yàn)?code>ct3結(jié)果包含3行數(shù)據(jù)，但是使用MAX,MIN得到一行結(jié)果。

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;
 
+----------------------------+----------------------+
| MAX(txt)                   | MIN(txt)             |
+----------------------------+----------------------+
| This is a query that rocks | This is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)

官方第二個(gè)示例是遞歸的用法，根據(jù)閱讀文檔，我分析下面查詢結(jié)果如下。
首先定義一個(gè)臨時(shí)表my_cte分析SELECT 1 AS n，這個(gè)是決定臨時(shí)表的列名為n，值為1
然后SELECT 1+n FROM my_cte WHERE n10，這個(gè)是遞歸查詢n10,并將1+n作為結(jié)果填充臨時(shí)表
最終使用SELECT * FROM my_cte，查詢臨時(shí)表，因此查詢出的結(jié)果就顯而易見了

WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n10
)
SELECT * FROM my_cte;
 
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0,00 sec)

根據(jù)我的理解寫了如下2個(gè)不一樣的查詢，查詢結(jié)果都一樣。
值得注意的是臨時(shí)表里面的多個(gè)查詢列數(shù)量和類型必須一樣，不然會(huì)報(bào)錯(cuò)。

這個(gè)是將臨時(shí)表列名指定在第一行
WITH RECURSIVE my_cte(a,b,c) AS
(
  SELECT 1,1,1
  UNION ALL
  SELECT 1+a,2+b,3+c FROM my_cte WHERE a10
)
SELECT * FROM my_cte;
 
這個(gè)第一行沒有指定列名，然后列名由第一個(gè)查詢返回結(jié)果確定
WITH RECURSIVE my_cte AS
(
  SELECT 1 AS a,1 AS b,1 AS c
  UNION ALL
  SELECT 1+a,2+b,3+c FROM my_cte WHERE a10
)
SELECT * FROM my_cte;

根據(jù)官方文檔，臨時(shí)表的語法模板如下，是可以有很多行的查詢共同組成。

WITH RECURSIVE cte_name [list of column names ] AS
(
  SELECT ...      -- specifies initial set
  UNION ALL
  SELECT ...      -- specifies initial set
  UNION ALL
  ...
  SELECT ...      -- specifies how to derive new rows
  UNION ALL
  SELECT ...      -- specifies how to derive new rows
  ...
)
[, any number of other CTE definitions ]

官方文檔還列出了，使用臨時(shí)表時(shí)可以增刪改查新表，具體可以去閱讀官方文檔。

3、練習(xí)

關(guān)于遞歸的練習(xí)主要用于表里面包含父節(jié)點(diǎn)id之類的，詳情可以參考下面的練習(xí)。
定義下面這樣的表，存儲(chǔ)每個(gè)區(qū)域（省、市、區(qū)）的id，名字及上級(jí)區(qū)域的pid

 
CREATE TABLE tb(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(64));
 
INSERT INTO tb VALUES('002', 0, '浙江省');
INSERT INTO tb VALUES('001', 0, '廣東省');
INSERT INTO tb VALUES('003', '002', '衢州市');
INSERT INTO tb VALUES('004', '002', '杭州市');
INSERT INTO tb VALUES('005', '002', '湖州市');
INSERT INTO tb VALUES('006', '002', '嘉興市');
INSERT INTO tb VALUES('007', '002', '寧波市');
INSERT INTO tb VALUES('008', '002', '紹興市');
INSERT INTO tb VALUES('009', '002', '臺(tái)州市');
INSERT INTO tb VALUES('010', '002', '溫州市');
INSERT INTO tb VALUES('011', '002', '麗水市');
INSERT INTO tb VALUES('012', '002', '金華市');
INSERT INTO tb VALUES('013', '002', '舟山市');
INSERT INTO tb VALUES('014', '004', '上城區(qū)');
INSERT INTO tb VALUES('015', '004', '下城區(qū)');
INSERT INTO tb VALUES('016', '004', '拱墅區(qū)');
INSERT INTO tb VALUES('017', '004', '余杭區(qū)');
INSERT INTO tb VALUES('018', '011', '金東區(qū)');
INSERT INTO tb VALUES('019', '001', '廣州市');
INSERT INTO tb VALUES('020', '001', '深圳市');
 
WITH RECURSIVE cte AS (
 SELECT id,name FROM tb WHERE id='002'
 UNION ALL
 SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid
) SELECT * FROM cte;

執(zhí)行結(jié)果：

分析結(jié)果包含第一行SELECT id,name FROM tb WHERE id='002'的數(shù)據(jù)，此時(shí)表中只有一行數(shù)據(jù)
然后連表查詢SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid，遞歸的將父節(jié)點(diǎn)數(shù)據(jù)放入臨時(shí)表
最終查詢出來的就是遞歸的結(jié)果。

到此這篇關(guān)于MySQL的WITH查詢詳情的文章就介紹到這了,更多相關(guān)MySQL的WITH查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySql8 WITH RECURSIVE遞歸查詢父子集的方法
mysql視圖之確保視圖的一致性（with check option）操作詳解
安裝mysql出錯(cuò)”A Windows service with the name MySQL already exists.“如何解決
MySQL 5.6 中TIMESTAMP with implicit DEFAULT value is deprecated錯(cuò)誤
MySQL錯(cuò)誤TIMESTAMP column with CURRENT_TIMESTAMP的解決方法
MySQL提示:The server quit without updating PID file問題的解決辦法
php SQL Injection with MySQL

標(biāo)簽：崇左蘭州黃山銅川湘潭仙桃衡水湖南

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL8.0的WITH查詢詳情》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。