type
status
date
slug
summary
tags
category
icon
password
前言
Common Table Expression (CTE) 是一個在大部分的 RDBMS 都有的特性，例如：ORACLE、SQL SERVER、POSTGERSQL 這些都早已支援，而 MySQL 直到 8.0 才引入了此功能，接下來就讓我們來一起了解 CTE 是什麼以及怎麼使用吧！
什麼是 CTE ?
CTE 是一個命名的臨時結果集，僅在單個 SQL 語句的執行範圍內存在，並且可以多次引用。
除了多次引用的特性，可以看到 CTE 看起來很像子查詢，實際上 CTE 也可以用普通的子查詢來實現取代，例如以下範例：
但和子查詢 (subquery) 相比，CTE 還具有以下優勢
可以多次引用
假設我們有以下表格：
我們想比較每一天和前一天相比銷售量的變化：
在這個例子中我們可以看到傳統的子查詢我們必須進行 2 次 GROUP BY 的子查詢才能進行兩天的比對，而 cte 只需要 1 次，這就是 cte 的可重複引用特點。
- 可讀性更佳
- 可以遞迴
怎麼用 CTE ?
WITH 語句
使用 WITH 語句來建構 CTE，以下為其結構和範例：
WITH 語句的用法與限制
以下是可以使用 WITH 語句的地方：
- 在 SELECT,UPDATE 和 DELETE 語句的開頭使用
- 在子查詢的開頭使用
- 語句中包含 SELECT 語句，並且必須在 SELECT 前方使用
以下是 WITH 語句的一些限制：
- 在同一個層級中只允許包含一個 WITH 子句，如下範例是不合法的：
如果要使其合法，請使用逗號分隔多個 cte 的定義
或者是當在不同層級中就可以再使用其他 WITH 子句，如下範例是合法的：
- CTE 可以引用在其之前定義的另一個 CTE，但不能引用在其之後定義的另一個 CTE。
這是為了避免產生相互遞規的 CTE，例如 CTE1 引用 CTE2 並且 CTE2 引用 CTE1 的情況。
CTE 中的欄位命名
可以透過在 cte_name 後方括號定義欄位名稱
否則會以第一個 SELECT 語句中的欄位名稱為主
Recursive CTE 怎麼用?
一個 Recursive CTE 會是以下結構：
- 在 cte 名稱前方以 WITH RECRUSIVE 為開頭
- cte 內部的第一個 SELECT 語句用來生成初始的 row
- 使用 UNION ALL 或 UNION DISTINCT 來分隔 2 個 SELECT 語句
- cte 內部的第二個 SELECT 語句需要在 FROM 中引用 cte 用來產生額外的 row
接著讓我們看一下以下範例：
在以上範例中我們透過
SELECT 1 產生了初始的行，隨後在第2個語句中 n(1) < 5 產出了 SELECT 2 的結果，並繼續遞迴 n(2) < 5 產生了 SELECT 3 的結果以此類推，直到 n(5) < 5 不成立終止的此語法的遞迴產出了 1~5 的結果集。注意事項
- 遞迴 CTE 產生的結果集，其欄位的型態由初始語句也就是第一個 SELECT 語句決定，以下範例：
可以看到因為 str 欄位的初始值為 abc ，因此該欄位被定義為 CHAR(3) 後續產生的 str 欄位超過 3 個字符從而導致截斷的狀況，可以透過再初始語句添加
CAST() 函數指定型態來避免。- 遞迴 SELECT 子句(也就是第2部分的 SELECT) 不可以包含以下結構：
- Aggregate function
- Window function
- GROUP BY
- ORDER BY
- DISTINCT
- LIMIT ? OFFSET ? (Before MySQL 8.0.19)
SQL 標準限制
MySQL 限制
注意：在 MySQL 8.0.19 之後的版本取消了不可使用
LIMIT ? OFFSET ? 的限制，其對結果集的影響與在 CTE 外層編寫 LIMIT ? OFFSET ? 相同，但相比之下會更有效率，因為當遞迴 SELECT 產生所需的行數後會立即停止繼續遞迴產生後續的行數。 - 遞迴 SELECT 子句中必須且只能在 FROM 子句中引用 1 次 CTE，而不是在其他子查詢內。
- 遞迴 SELECT 子句可以引用其他的 TABLE 進行 JOIN，但是 CTE 不能為用 LEFT JOIN 的右側。
- EXPLAN 顯示的成本估算代表每次迭代的成本，因為優化器無法預測迭代次數，因此無法顯示總成本。
限制遞迴 CTE
- 透過以下 system variable 限制遞迴 CTE 的執行
- cte_max_recursion_depth：當遞迴次數超過此設定，會將改語句終止。
- max_execution_time：以毫秒為單位，當 SELECT 語句執行時間超過將 timeout。
- 透過 optimizer hint 限制單個語句的遞迴 CTE 執行
- 從 MySQL 8.0.19 開始可以透過 LIMIT 限制遞迴 CTE 產生的行數
範例
Date Series Generation
初始資料如下：
列出 2017/01/03 ~ 2017/01/10 每一天的銷售額 (銷後額為 0 也需要顯示)：
Hierarchical Data Traversal
初始資料如下：
列出所有員工從最上層的主管到自身的路徑圖：
CTE 運作過程如下事例：