CTE - PolarDB - 阿里雲

本文介紹CTE的功能和命令格式。

功能介紹

CTE（Common Table Expressions）表示通用資料表運算式，是一個臨時命名結果集，用於簡化SQL。是一個statement層級的子句運算式，以WITH開頭，後跟運算式名稱。包括以下兩類：

Recursive CTE表示CTE可以迭代訪問自身的情境，能夠實現SQL的遞迴查詢功能。
Non Recursive CTE表示非遞迴CTE，即CTE不使用遞迴，不迭代訪問自己。

文法

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

Recursive CTE

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

Non Recursive CTE

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;

約束

recursive part不能包含彙總函式、視窗函數、GROUP BY、ORDER BY、DISTINCT；
recursive part中訪問CTE自身只能訪問一次，並且只能在FROM子句訪問，不可以在子查詢中訪問；
recursive part子句中訪問CTE時，CTE不可以在left join的右側。

說明

Recursive CTE子句必須以WITH RECURSIVE開頭，否則會提示報錯資訊ERROR 1146 (42S02): Table 'cte_name' doesn't exist；
如果CTE沒有指向自己形成迭代，RECURSIVE可以有但無效果；
Recursive CTE包含兩部分查詢子句，通過UNION ALL或UNION串連：
```
SELECT ...      -- non recursive part, return initial row set
UNION [ALL]
SELECT ...      -- recursive part, return additional row sets				
```
其中第一個查詢子句non recursive part負責產生初始資料，這個查詢子句不能指向CTE自身。第二個查詢子句產生附加行，並且會在FROM子句中訪問CTE自身。迭代執行會在第二個查詢子句recursive part無法查出新資料時停止；
Recursive CTE的傳回型別由non recursive part決定，並且全部為nullable；
每輪迭代時，recursive part引用自身的資料僅限上一次迭代產生的資料，而非之前產生的全部資料；

變數基於變數名和位置決定在每輪迭代中的賦值：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

如上所示，n、p、q變數在non recursive part賦值後，在recursive part中基於變數名去做賦值。recursive part執行完成以後，基於project中的位置對變數賦值。因此以上樣本的輸出為：

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

通過limit控制recursive次數，即recursive cte的union子句後面可以接limit。

參數說明

cte_max_recursion_depth：recursive part子句迭代執行次數的上限，預設值為500。