您好,登錄后才能下訂單哦!
之前寫過一篇 MySQL通過自定義函數(shù)的方式,遞歸查詢樹結(jié)構(gòu),從MySQL 8.0 開始終于支持了遞歸查詢的語法
CTE
首先了解一下什么是 CTE,全名 Common Table Expressions
WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
cte1, cte2 為我們定義的CTE,可以在當前查詢中引用
可以看出 CTE 就是一個臨時結(jié)果集,和派生表類似,二者的區(qū)別這里不細說,可以參考下MySQL開發(fā)文檔:https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-examples
遞歸查詢
先來看下遞歸查詢的語法
WITH RECURSIVE cte_name AS ( SELECT ... -- return initial row set UNION ALL / UNION DISTINCT SELECT ... -- return additional row sets ) SELECT * FROM cte;
遞歸部分不能包括:
上面的講解可能有點抽象,通過例子慢慢來理解
WITH RECURSIVE cte (n) AS -- 這里定義的n相當于結(jié)果集的列名,也可在下面查詢中定義 ( SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 5 ) SELECT * FROM cte; -- result +------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+
EXAMPLE
最后來看一個樹結(jié)構(gòu)的例子
CREATE TABLE `c_tree` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql> select * from c_tree; +----+---------+-----------+ | id | cname | parent_id | +----+---------+-----------+ | 1 | 1 | 0 | | 2 | 2 | 0 | | 3 | 3 | 0 | | 4 | 1-1 | 1 | | 5 | 1-2 | 1 | | 6 | 2-1 | 2 | | 7 | 2-2 | 2 | | 8 | 3-1 | 3 | | 9 | 3-1-1 | 8 | | 10 | 3-1-2 | 8 | | 11 | 3-1-1-1 | 9 | | 12 | 3-2 | 3 | +----+---------+-----------+
mysql> WITH RECURSIVE tree_cte as ( select * from c_tree where parent_id = 3 UNION ALL select t.* from c_tree t inner join tree_cte tcte on t.parent_id = tcte.id ) SELECT * FROM tree_cte; +----+---------+-----------+ | id | cname | parent_id | +----+---------+-----------+ | 8 | 3-1 | 3 | | 12 | 3-2 | 3 | | 9 | 3-1-1 | 8 | | 10 | 3-1-2 | 8 | | 11 | 3-1-1-1 | 9 | +----+---------+-----------+
更多信息
https://dev.mysql.com/doc/refman/8.0/en/with.html
以上就是本文的全部內(nèi)容,希望對大家的學習有所幫助,也希望大家多多支持億速云。
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。