MySQL8.0的WITH查詢詳情
關于MySQL8的WITH查詢學習
：
對于邏輯復雜的sql，with可以大大減少臨時表的數(shù)量，提升代碼的可讀性、可維護性
MySQL 8.0終于開始支持with語句了，對于復雜查詢，可以不用寫那么多的臨時表了。
可以查看官方文檔【點擊跳轉】
1、示例
官方第一個示例，可以看出該查詢語句創(chuàng)建了cte1,cte2,cte3,cte4這4個臨時表，后面的臨時表依賴前面的臨時表數(shù)據(jù)。
最后一行為最終查詢結果，實際ct4因為ct3結果包含3行數(shù)據(jù)，但是使用MAX,MIN得到一行結果。
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)
官方第二個示例是遞歸的用法，根據(jù)閱讀文檔，我分析下面查詢結果如下。
首先定義一個臨時表my_cte分析SELECT 1 AS n，這個是決定臨時表的列名為n，值為1
然后SELECT 1+n FROM my_cte WHERE n<10，這個是遞歸查詢n<10,并將1+n作為結果填充臨時表
最終使用SELECT * FROM my_cte，查詢臨時表，因此查詢出的結果就顯而易見了
WITH RECURSIVE my_cte AS ( SELECT 1 AS n UNION ALL SELECT 1+n FROM my_cte WHERE n<10 ) SELECT * FROM my_cte; +------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +------+ 10 rows in set (0,00 sec)
根據(jù)我的理解寫了如下2個不一樣的查詢，查詢結果都一樣。
值得注意的是臨時表里面的多個查詢列數(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 a<10 ) SELECT * FROM my_cte; 這個第一行沒有指定列名，然后列名由第一個查詢返回結果確定 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 a<10 ) SELECT * FROM my_cte;
根據(jù)官方文檔，臨時表的語法模板如下，是可以有很多行的查詢共同組成。
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 ]
官方文檔還列出了，使用臨時表時可以增刪改查新表，具體可以去閱讀官方文檔。
3、練習
關于遞歸的練習主要用于表里面包含父節(jié)點id之類的，詳情可以參考下面的練習。
定義下面這樣的表，存儲每個區(qū)域（省、市、區(qū)）的id，名字及上級區(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', '臺州市');
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í)行結果：
分析結果包含第一行SELECT id,name FROM tb WHERE id='002'的數(shù)據(jù)，此時表中只有一行數(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é)點數(shù)據(jù)放入臨時表
最終查詢出來的就是遞歸的結果。
到此這篇關于MySQL的WITH查詢詳情的文章就介紹到這了,更多相關MySQL的WITH查詢內容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站！
版權聲明：本站文章來源標注為YINGSOO的內容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
關注官方微信