如何在SELECT語句中使用WITH子句 - AnalyticDB

查詢中可以使用WITH子句來建立通用運算式（Common Table Expression，簡稱CTE）。CTE是一個臨時結果集，在單個SQL語句執行期間存在，供SELECT查詢引用。CTE可以扁平化巢狀查詢或者簡化子查詢，SELECT只需執行一遍子查詢，提高查詢效能。本文介紹如何在SELECT查詢中使用WITH子句。

注意事項

CTE之後可以接SQL語句或者其他的CTE（只能使用一個WITH），多個CTE中間用逗號（,）分隔，否則CTE將失效。
CTE語句中暫不支援分頁功能。

WITH使用方法

以下兩個查詢等價

SELECT a, b
FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;

WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

WITH子句可用於多子查詢

WITH
t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1 JOIN t2 ON t1.a = t2.a;

WITH子句中定義的關係可以互相串連

WITH
x AS (SELECT a FROM t),
y AS (SELECT a AS b FROM x),
z AS (SELECT b AS c FROM y)
SELECT c FROM z;

遞迴查詢
```
WITH RECURSIVE cte (n) AS (
   SELECT 1
   UNION ALL
   SELECT n + 1 FROM cte WHERE n < 15 
)
SELECT * FROM cte;
```
預設情況下，遞迴深度限制為10，即允許遞迴調用10次。如需調整該限制，可通過命令SET ADB_CONFIG max_recursion_depth=value在全域範圍內修改遞迴調用次數，或通過Hint/*max_recursion_depth=value*/在查詢層級修改遞迴調用次數。其中，value取值應為大於0的正整數。

CTE執行最佳化

3.1.9.3及以上核心版本的叢集支援使用CTE執行最佳化功能，該功能預設關閉，您可以通過設定CTE_EXECUTION_MODE配置項手動開啟該功能。功能開啟後，CTE子查詢被多次引用時，只執行一次，提升部分查詢的查詢效能。

重要

開啟CTE執行最佳化功能後可能會導致部分查詢的查詢效能會下降，若您發現查詢效能有明顯下降，建議您關閉CTE執行最佳化。

開啟CTE執行最佳化

對指定查詢開啟CTE執行最佳化

在指定查詢語句前添加Hint，僅對該查詢開啟CTE執行最佳化。內建資料集CTE執行最佳化，樣本如下：

/*cte_execution_mode=shared*/ 
WITH shared AS (SELECT L_ORDERKEY，L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;

對所有查詢開啟CTE執行最佳化
執行SET語句對所有查詢開啟CTE執行最佳化，樣本如下：
```
SET adb_config cte_execution_mode=shared;
```

關閉CTE執行最佳化

對指定查詢關閉CTE執行最佳化

在指定查詢語句前添加Hint，僅對該查詢關閉CTE執行最佳化。內建資料集CTE執行最佳化，樣本如下：

/*cte_execution_mode=inline*/ 
WITH shared AS (SELECT L_ORDERKEY，L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;

對所有查詢關閉CTE執行最佳化
執行SET語句對所有查詢關閉CTE執行最佳化，樣本如下：
```
SET adb_config cte_execution_mode=inline;
```