MariaDB表表達式(2)：CTE

来源:https://www.cnblogs.com/f-ck-need-u/archive/2018/04/18/8875863.html
-Advertisement-
Play Games

本文目錄： 1.非遞歸CTE 2.遞歸CTE 2.1 語法 2.2 遞歸CTE示例(1) 2.3 遞歸CTE示例(2) 2.4 遞歸CTE示例(3) 公用表表達式（Common Table Expression，CTE）和派生表類似，都是虛擬的表，但是相比於派生表，CTE具有一些優勢和方便之處。 C ...


本文目錄：
1.非遞歸CTE
2.遞歸CTE
　2.1 語法
　2.2 遞歸CTE示例(1)
　2.3 遞歸CTE示例(2)
　2.4 遞歸CTE示例(3)

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

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

CTE是標準SQL的特性，屬於表表達式的一種，MariaDB支持CTE，MySQL不支持。

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
)
# 使用CTE
SELECT * 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 t2
WHERE t1.myid = t2.myid+1;
 
# 多次定義，避免派生表嵌套
WITH
nv_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   目標：stopA
stopA-->stopC-->stopD   目標：stopD
stopA-->stopC-->stopB   目標：stopB

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

 

回到Linux系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到網站架構系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到資料庫系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
轉載請註明出處：http://www.cnblogs.com/f-ck-need-u/p/8875863.html

註：若您覺得這篇文章還不錯請點擊右下角推薦，您的支持能激發作者更大的寫作熱情，非常感謝！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL數據類型/屬性/增刪改查（14）
    MySQL數據類型 日期類型 ·date date數據類型負責存儲日期信息（1000-01-01到9999-12-31）可以使用數字和字元串插入（20180809或"2018-08-09"）非數字或字母使用分隔符 ·datetime datetime數據類型負責存儲日期和時間信息的組合（1000-0 ...
  • 什麼是Azkaban?
    Azkaban是什麼 Azkaban是由Linkedin開源的做批量工作流任務的調度器。在一個工作流內按照特定的順序運行一組工作和流程。Azkaban定義了一種KV文件格式來建立任務之間的相互依賴關係，並且提供了一個易於使用的web用戶界面維護與跟蹤你的工作流。 Azkaban的功能特點： web用 ...
  • PL/SQL的安裝
    一、下載PLSQLDeveloper 網址：https://www.allroundautomations.com/bodyplsqldevreg.html 還需要一個激活碼 二、PL/SQL的安裝 安裝PL/SQL步驟如下： 運行這個程式時 自動讀取(環境變數ORACLE_HOME以及NLS_LA ...
  • mysql之事務管理
    本文內容： 什麼是事務管理 事務管理操作 回滾點 預設的事務管理 首發日期：2018-04-18 什麼是事務管理： 可以把一系列要執行的操作稱為事務，而事務管理就是管理這些操作要麼完全執行，要麼完全不執行（很經典的一個例子是：A要給B轉錢，首先A的錢減少了，但是突然的資料庫斷電了，導致無法給B加錢， ...
  • MongoDB最佳實踐中文手冊
    背景：查閱了一下MongoDB的相關文檔，發現中文文檔還是比較少的，工作中需要用到MongoDB，而這本《MongoDB最佳實踐》是很好的選擇，所以就把這本手冊翻譯了一下，其中生澀的專業用語是參考MongoDB中文官網進行翻譯，校對的時間比較少，難免會有不合理的地方，懇請大家指正。 簡介 Mongo ...
  • mysql中的範式
    範式 範式：Normal Format，是一種離散數學中的知識，是為瞭解決數據的存儲與優化的問題：保存數據的存儲之後，凡是能夠通過關係尋找出來的數據，堅決不再重覆存儲，終極目標是為了減少數據的冗餘。範式：是一種分層結構的規範，分為六層，每一層都比上一層更加嚴格，若要滿足下一層範式，前提是滿足上一層範 ...
  • Sql Server2008R2下載地址
    ed2k://%7Cfile%7Ccn_sql_server_2008_r2_enterprise_x86_x64_ia64_dvd_522233.iso%7C4662884352%7C1DB025218B01B48C6B76D6D88630F541%7C/ ...
  • linux下怎麼進入本機mysql
    sudo /etc/init.d/mysql startmysql -u xxxx -p*****mysql >_ ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類