文章詳情頁

MariaDB表表達式之公用表表達式(CTE)

瀏覽：125日期：2023-03-30 13:21:09

前言

公用表表達式（Common Table Expression，CTE）和派生表類似，都是虛擬的表，但是相比于派生表，CTE具有一些優勢和方便之處。

CTE有兩種類型：非遞歸的CTE和遞歸CTE。

CTE是標準SQL的特性，屬于表表達式的一種，MariaDB支持CTE，MySQL 8才開始支持CTE。

1.非遞歸CTE

CTE是使用WITH子句定義的，包括三個部分：CTE名稱cte_name、定義CTE的查詢語句inner_query_definition和引用CTE的外部查詢語句outer_query_definition。

它的格式如下：

WITH cte_name1[(column_name_list)] AS (inner_query_definition_1)   [,cte_name2[(column_name_list)] AS (inner_query_definition_2)][,...]outer_query_definition

其中column_name_list指定inner_query_definition中的列列表名，如果不寫該選項，則需要保證在inner_query_definition中的列都有名稱且唯一，即對列名有兩種命名方式：內部命名和外部命名。

注意，outer_quer_definition必須和CTE定義語句同時執行，因為CTE是臨時虛擬表，只有立即引用它，它的定義才是有意義的。

下面語句是一個簡單的CTE的用法。首先定義一張虛擬表，也就是CTE，然后在外部查詢中引用它。

CREATE OR REPLACE TABLE t(id INT NOT NULL PRIMARY KEY,sex CHAR(3),NAME CHAR(20));INSERT INTO t VALUES (1,"nan","David"),(2,"nv","Mariah"),(3,"nv","gaoxiaofang"),(4,"nan","Jim"),(5,"nv","Selina"),(6,"nan","John"),(7,"nan","Monty"),(8,"nv","xiaofang"); # 定義CTE，順便為每列重新命名，且使用ORDER BY子句WITH nv_t(myid,mysex,myname) AS (    SELECT * FROM t WHERE sex="nv" ORDER BY id DESC)# 使用CTESELECT * FROM nv_t;+------+-------+-------------+| myid | mysex | myname      |+------+-------+-------------+|    2 | nv    | Mariah      ||    3 | nv    | gaoxiaofang ||    5 | nv    | Selina      ||    8 | nv    | xiaofang    |+------+-------+-------------+

從結果中可以看到，在CTE的定義語句中使用ORDER BY子句是沒有任何作用的。

在這里可以發現，CTE和派生表需要滿足的幾個共同點：每一列要求有列名，包括計算列；列名必須唯一；不能使用ORDER BY子句，除非使用了TOP關鍵字(標準SQL嚴格遵守不能使用ORDER BY的規則，但MySQL/MariaDB中允許)。不僅僅是CTE和派生表，其他表表達式(內聯表值函數(sql server才支持)、視圖)也都要滿足這些條件。究其原因，表表達式的本質是表，盡管它們是虛擬表，也應該滿足形成表的條件。

一方面，在關系模型中，表對應的是關系，表中的行對應的是關系模型中的元組，表中的字段（或列）對應的是關系中的屬性。屬性由三部分組成：屬性的名稱、屬性的類型和屬性值。因此要形成表，必須要保證屬性的名稱，即每一列都有名稱，且唯一。

另一方面，關系模型是基于集合的，在集合中是不要求有序的，因此不能在形成表的時候讓數據按序排列，即不能使用ORDER BY子句。之所以在使用了TOP后可以使用ORDER BY子句，是因為這個時候的ORDER BY只為TOP提供數據的邏輯提取服務，并不提供排序服務。例如使用ORDER BY幫助TOP選擇出前10行，但是這10行數據在形成表的時候不保證是順序的。

相比派生表，CTE有幾個優點：

1.多次引用：避免重復書寫。

2.多次定義：避免派生表的嵌套問題。

3.可以使用遞歸CTE，實現遞歸查詢。

例如：

# 多次引用，避免重復書寫WITH nv_t(myid,mysex,myname) AS (    SELECT * FROM t WHERE sex="nv")SELECT t1.*,t2.*FROM nv_t t1 JOIN nv_t t2WHERE t1.myid = t2.myid+1; # 多次定義，避免派生表嵌套WITHnv_t1 AS (  /* 第一個CTE */    SELECT * FROM t WHERE sex="nv" ),nv_t2 AS (  /* 第二個CTE */    SELECT * FROM nv_t1 WHERE id>3)SELECT * FROM nv_t2;

如果上面的語句不使用CTE而使用派生表的方式，則它等價于：

SELECT * FROM(SELECT * FROM(SELECT * FROM t WHERE sex="nv") AS nv_t1) AS nv_t2;

2.遞歸CTE

SQL語言是結構化查詢語言，它的遞歸特性非常差。使用遞歸CTE可稍微改善這一缺陷。

公用表表達式(CTE)具有一個重要的優點，那就是能夠引用其自身，從而創建遞歸CTE。遞歸CTE是一個重復執行初始CTE以返回數據子集直到獲取完整結果集的公用表表達式。

當某個查詢引用遞歸CTE時，它即被稱為遞歸查詢。遞歸查詢通常用于返回分層數據，例如：顯示某個組織圖中的雇員或物料清單方案(其中父級產品有一個或多個組件，而那些組件可能還有子組件，或者是其他父級產品的組件)中的數據。

遞歸CTE可以極大地簡化在SELECT、INSERT、UPDATE、DELETE或CREATE VIEW語句中運行遞歸查詢所需的代碼。

也就是說，遞歸CTE通過引用自身來實現。它會不斷地重復查詢每一次遞歸得到的子集，直到得到最后的結果。這使得它非常適合處理"樹狀結構"的數據或者有"層次關系"的數據。

2.1 語法

遞歸cte中包含一個或多個定位點成員，一個或多個遞歸成員，最后一個定位點成員必須使用"union [all]"(mariadb中的遞歸CTE只支持union [all]集合算法)聯合第一個遞歸成員。

以下是單個定位點成員、單個遞歸成員的遞歸CTE語法：

with recursive cte_name as (    select_statement_1       /* 該cte_body稱為定位點成員 */  union [all]    cte_usage_statement      /* 此處引用cte自身，稱為遞歸成員 */)outer_definition_statement    /* 對遞歸CTE的查詢，稱為遞歸查詢 */

其中：

select_statement_1：稱為"定位點成員"，這是遞歸cte中最先執行的部分，也是遞歸成員開始遞歸時的數據來源。

cte_usage_statement：稱為"遞歸成員"，該語句中必須引用cte自身。它是遞歸cte中真正開始遞歸的地方，它首先從定位點成員處獲取遞歸數據來源，然后和其他數據集結合開始遞歸，每遞歸一次都將遞歸結果傳遞給下一個遞歸動作，不斷重復地查詢后，當最終查不出數據時才結束遞歸。

outer_definition_statement：是對遞歸cte的查詢，這個查詢稱為"遞歸查詢"。

2.2 遞歸CTE示例(1)

舉個最經典的例子：族譜。

例如，下面是一張族譜表

CREATE OR REPLACE TABLE fork(id INT NOT NULL UNIQUE,NAME CHAR(20),father INT,mother INT);INSERT INTO fork VALUES    (1,"chenyi",2,3),(2,"huagner",4,5),(3,"zhangsan",NULL,NULL),    (4,"lisi",6,7),(5,"wangwu",8,9),(6,"zhaoliu",NULL,NULL),(7,"sunqi",NULL,NULL),    (8,"songba",NULL,NULL),(9,"yangjiu",NULL,NULL); MariaDB [test]> select * from fork;+----+----------+--------+--------+| id | name     | father | mother |+----+----------+--------+--------+|  1 | chenyi   |      2 |      3 ||  2 | huagner  |      4 |      5 ||  3 | zhangsan |   NULL |   NULL ||  4 | lisi     |      6 |      7 ||  5 | wangwu   |      8 |      9 ||  6 | zhaoliu  |   NULL |   NULL ||  7 | sunqi    |   NULL |   NULL ||  8 | songba   |   NULL |   NULL ||  9 | yangjiu  |   NULL |   NULL |+----+----------+--------+--------+

該族譜表對應的結構圖：

如果要找族譜中某人的父系，首先在定位點成員中獲取要從誰開始找，例如上圖中從"陳一"開始找。那么陳一這個記錄就是第一個遞歸成員的數據源，將這個數據源聯接族譜表，找到陳一的父親黃二，該結果將通過union子句結合到上一個"陳一"中。再次對黃二遞歸，找到李四，再對李四遞歸找到趙六，對趙六遞歸后找不到下一個數據，所以這一分支的遞歸結束。

遞歸cte的語句如下：

WITH recursive fuxi AS (    SELECT * FROM fork WHERE `name`="chenyi"    UNION    SELECT f.* FROM fork f JOIN fuxi a WHERE f.id=a.father)SELECT * FROM fuxi;

演變結果如下：

首先執行定位點部分的語句，得到定位點成員，即結果中的第一行結果集：

根據該定位點成員，開始執行遞歸語句：

遞歸時，按照f.id=a.father的條件進行篩選，得到id=2的結果，該結果通過union和之前的數據結合起來，作為下一次遞歸的數據源fuxi。

再進行第二次遞歸：

第三次遞歸：

由于第三次遞歸后，id=6的father值為null，因此第四次遞歸的結果為空，于是遞歸在第四次之后結束。

2.2 遞歸CTE示例(2)

該CTE示例主要目的是演示切換遞歸時的字段名稱。

例如，有幾個公交站點，它們之間的互通性如下圖：

對應的表為：

CREATE OR REPLACE TABLE bus_routes (src char(50), dst char(50));INSERT INTO bus_routes VALUES   ("stopA","stopB"),("stopB","stopA"),("stopA","stopC"),("stopC","stopB"),("stopC","stopD");MariaDB [test]> select * from bus_routes;+-------+-------+| src   | dst   |+-------+-------+| stopA | stopB || stopB | stopA || stopA | stopC || stopC | stopB || stopC | stopD |+-------+-------+

要計算以stopA作為起點，能到達哪些站點的遞歸CTE如下：

WITH recursive dst_stop AS (    SELECT src AS dst FROM bus_routes WHERE src="stopA"   /* note: src as dst */    UNION    SELECT b.dst FROM bus_routes b       JOIN dst_stop d     WHERE d.dst=b.src)SELECT * FROM dst_stop;

結果如下：

+-------+| dst   |+-------+| stopA || stopB || stopC || stopD |+-------+

首先執行定位點語句，得到定位點成員stopA，字段名為dst。

再將定位點成員結果和bus_routes表聯接進行第一次遞歸，如下圖：

再進行第二次遞歸：

再進行第三次遞歸，但第三次遞歸過程中，stopD找不到對應的記錄，因此遞歸結束。

2.2 遞歸CTE示例(3)

仍然是公交路線圖：

計算以stopA為起點，可以到達哪些站點，并給出路線圖。例如:stopA-->stopC-->stopD。

以下是遞歸CTE語句：

WITH recursive bus_path(bus_path,bus_dst) AS (    SELECT src,src FROM bus_routes WHERE src="stopA"    UNION    SELECT CONCAT(b2.bus_path,"-->",b1.dst),b1.dst    FROM bus_routes b1      JOIN bus_path b2    WHERE b2.bus_dst = b1.src AND LOCATE(b1.dst,b2.bus_path)=0)SELECT * FROM bus_path;

首先獲取起點stopA，再獲取它的目標stopB和stopC，并將起點到目標使用"-->"連接，即concat(src,"-->","dst")。再根據stopB和stopC，獲取它們的目標。stopC的目標為stopD和stopB，stopB的目標為stopA。如果連接成功，那么路線為：

stopA-->stopB-->stopA   目標：stopAstopA-->stopC-->stopD   目標：stopDstopA-->stopC-->stopB   目標：stopB

這樣會無限遞歸下去，因此我們要判斷何時結束遞歸。判斷的方法是目標不允許出現在路線中，只要出現，說明路線會重復計算。

總結

到此這篇關于MariaDB表表達式之公用表表達式(CTE)的文章就介紹到這了,更多相關MariaDB公用表表達式CTE內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！

MariaDB

上一條：MariaDB Spider 數據庫分庫分表實踐記錄下一條：debian10 mariadb安裝過程詳解