目前很缺遞迴思維，主要是演算法程式碼寫得少，本篇記錄下最近思考的內容。以 PostgreSQL 程式碼舉例（主要是非常喜歡這款效能小鋼炮資料庫）。
樹狀查詢不多說，很簡單大家基本都會，主要講 cte 程式碼遞迴實現不同需求。
以下所有內容都是我個人理解，不對之處請各位讀者多指教！
cte 語法簡介
以PG舉例，如果是ORACLE的話需要去掉RECURSIVE關鍵字
WITH RECURSIVE cte_tb (column_list) AS (
-- 開始條件
SELECT ...
UNION ALL
-- 遞迴邏輯程式碼和結束遞迴邏輯的程式碼
SELECT ... FROM cte_tb WHERE ...
)
SELECT * FROM cte_tb;
PG使用 cte 遞迴實現層級查詢
scott=> WITH RECURSIVE T(LV, EMPNO, ENAME, MGR) AS (
scott(> SELECT 1 AS LV, EMPNO, ENAME, MGR FROM EMP WHERE MGR IS NULL -- 根節點，（開始條件）
scott(> UNION ALL
scott(> SELECT T.LV + 1, E.EMPNO, E.ENAME, E.MGR FROM EMP E
scott(> INNER JOIN T ON T.EMPNO = E.MGR -- e表屬於子節點，t表屬於上層節點 t.EMPNO = e.MGR 相當於 prior empno = mgr; （遞迴條件），如果 t.EMPNO = e.MGR 匹配不上了就返回NULL （遞迴結束條件）
scott(> )
scott-> SELECT *
scott-> FROM T;
lv | empno | ename | mgr
----+-------+--------+------
1 | 7839 | KING |
2 | 7566 | JONES | 7839
2 | 7698 | BLAKE | 7839
2 | 7782 | CLARK | 7839
3 | 7499 | ALLEN | 7698
3 | 7521 | WARD | 7698
3 | 7654 | MARTIN | 7698
3 | 7788 | SCOTT | 7566
3 | 7844 | TURNER | 7698
3 | 7900 | JAMES | 7698
3 | 7902 | FORD | 7566
3 | 7934 | MILLER | 7782
4 | 7369 | SMITH | 7902
4 | 7876 | ADAMS | 7788
(14 rows)
Time: 0.396 ms
cte 遞迴核心思想
一、使用 cte 遞迴，一定要滿足以下三個條件：
- 開始條件。
- 遞迴條件。
- 遞迴結束條件。
二、遞迴重要的思想：
- 大問題拆小問題，這個比較難，（怎麼拆、小問題之間的邏輯如何關聯上，遞迴結束條件如何滿足）等， 這也主要是我缺乏遞迴思維原因。
- 遞迴和迴圈的思路是高度相似：
- 迴圈需要 開始條件、結束條件、迴圈邏輯。
- 遞迴需要 開始條件、結束條件、遞迴邏輯+呼叫自身邏輯。
案例一、cte 遞迴實現數字遞增：
with RECURSIVE x(seq) as (
SELECT 1 as seq -- SELECT 1 as seq from DUAL 遞迴開始條件
UNION ALL
SELECT x.seq + 1 as seq from x -- x.seq + 1 from x 遞迴條件（每次執行 + 1 ） 呼叫自身
WHERE x.seq < 10 -- x.seq < 10 遞迴結束條件
)
SELECT * FROM x ORDER BY 1;
seq
-----
1
2
3
4
5
6
7
8
9
10
(10 rows)
Time: 0.700 ms
上面這個案例很像迴圈，但是總體實現起來整體的思路會比迴圈稍微複雜那麼一丟丟。
其實在 PG 來說實現數字遞增的方式很多，例如：序列、SERIAL 、PLPG/SQL for 迴圈， 均能實現類似效果，上面案例案例讓各位讀者初步感受下。
案例二、cte 遞迴實現distinct效果
distinct sql
select distinct col from tt2;
col
--------
C
JAVA
PL/SQL
Python
(4 rows)
Time: 255.794 ms
使用CTE遞迴的方式實現
WITH RECURSIVE t(col) as (
(SELECT col from tt2 ORDER BY col LIMIT 1) -- 遞迴開始條件。
UNION ALL
SELECT (SELECT col FROM tt2 WHERE tt2.COL > t.COL order by tt2.COL LIMIT 1) -- tt2.COL > t.COL 大問題拆小問題 ，遞迴邏輯
FROM t WHERE t.COL IS NOT NULL -- 遞迴結束條件
)
SELECT * FROM t WHERE t.COL is not NULL ;
col
--------
C
JAVA
PL/SQL
Python
(4 rows)
Time: 0.871 ms
這個案例引用的是德哥的思路，PG 15 上對 distinct 運算元最佳化過（支援並行），一千萬行資料 265 ms 就能跑出結果。
但是如果使用 cte 遞迴的話，根本不需要並行，0.8 ms 便能出結果，秒殺最佳化器演算法。
這個 order by tt2.col 非常牛逼，神來之筆，相當於進一步最佳化了整個遞迴的演算法模型。
基於德哥的思路做了修改
WITH RECURSIVE t(col) as (
(SELECT col from tt2 ORDER BY col LIMIT 1)
UNION ALL
SELECT (SELECT col FROM tt2 WHERE tt2.COL > t.COL GROUP BY tt2.COL LIMIT 1) FROM t WHERE t.COL IS NOT NULL
)
SELECT * FROM t WHERE t.COL is not NULL ;
col
--------
C
JAVA
PL/SQL
Python
(4 rows)
Time: 0.432 ms
order by 改成 group by 是借鑑德哥思路，我自己想的改良版，速度提升了 0.4ms , 不過總體來說差不多，有真實案例看場景使用。
案例三、cte 遞迴實現階乘演算法：
WITH RECURSIVE factorial (n, factorial_val) AS (
(SELECT 1 n, 1 factorial_val ) -- 遞迴開始條件 : 1的階乘為1
UNION ALL
SELECT f.n + 1, (f.n + 1) * f.factorial_val /* 遞迴邏輯 (1 + 1) * 1 = 2
(2 + 1) * 2 = 6
(3 + 1) * 6 = 24
(4 + 1) * 24 = 120
*/
FROM factorial f
WHERE f.n < 5 -- 結束遞迴條件，算 5 的階乘
)
SELECT max(factorial_val) FROM factorial;
max
-----
120
(1 row)
Time: 0.395 ms
CTE 遞迴也能實現階乘的邏輯，由於 PG 上是沒階乘函式的，可以將上面邏輯封裝到一個函式里面進行使用，程式碼如下：
CREATE OR REPLACE FUNCTION factorial(num BIGINT)
RETURNS BIGINT AS $$
DECLARE
result BIGINT;
BEGIN
WITH RECURSIVE factorial (n, factorial_val) AS (
(SELECT 1::INT as n , 1::int as factorial_val)
UNION ALL
SELECT f.n + 1, (f.n + 1) * f.factorial_val
FROM factorial f
WHERE f.n < num
)
SELECT max(factorial_val) INTO result FROM factorial;
RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
結束語
cte 遞迴的技巧在任何資料庫都通用，我這裡只是使用了PG作為演示案例，遞迴不僅僅是樹狀查詢，理論上來說，只要能拆解邏輯（這也是最難的），所有SQL邏輯都能使用遞迴來表達。
但是這玩意是個雙刃劍，不是所有場景都能使用，假如一個列的選擇性很高，例如主鍵，如果使用遞迴來進行匹配查詢的話，那絕對是SB行為，線性遞迴的時間複雜度是O(n)，速度取決於你的資料量。
沒有最好的演算法，只有最合適的演算法。不過有遞迴思維的話，確實能解決很多日常和工作中不同型別的事物。