遞迴 CTE：這是什麼？為什麼要用？

今天我們再往前一步，來玩玩看遞迴的魔法。如果你以前有寫過支援遞迴的語言（像是 Python），大概知道這是什麼意思。不過別擔心，就算你覺得這聽起來很神秘，我們會講得超詳細。

遞迴 CTE 是處理階層式、樹狀資料結構的超強工具，像是公司的組織架構、家族樹、或是檔案目錄這種。

簡單說，就是一種「可以自己呼叫自己」的查詢，讓你可以一層一層地把所有資料都處理完。

遞迴 CTE 的重點特色：

1. 它們會用到 WITH RECURSIVE 這個關鍵字。
2. 遞迴 CTE 分成兩個部分：
   • 基礎查詢：決定遞迴的起點（或叫「根」）。
   • 遞迴查詢：用前一步的結果來處理剩下的資料。

遞迴 CTE 的運作邏輯很像你在爬樓梯：

• 你先站上第一階（這就是基礎查詢）。
• 然後用第一階的結果爬到第二階（遞迴查詢）。
• 這個動作一直重複，直到樓梯沒了（達到結束條件）。

遞迴 CTE 的語法

直接來看個範本：

WITH RECURSIVE cte_name AS (
    -- 基礎查詢
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- 遞迴查詢
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

UNION 跟 UNION ALL 在遞迴 CTE 裡的角色

每個遞迴 CTE 一定要在基礎跟遞迴部分之間用 UNION 或 UNION ALL。

該選 UNION 還是 UNION ALL？

如果你不確定要用哪個，幾乎都選 UNION ALL。為什麼？因為它比較快：它只是單純合併結果，不會去檢查有沒有重複。這代表計算量少、資源省、速度快。

這點在 遞迴 CTE 裡特別重要。你在建階層結構時——像是留言樹、公司下屬結構——幾乎都要用 UNION ALL。如果用 UNION，資料庫可能會誤以為某些步驟已經做過，結果「砍掉」一部分結果，整個邏輯就壞掉了。

只有在你很確定重複資料會害到你，而且一定要去掉時，才用 UNION。但記住：這永遠是「乾淨」跟「速度」的取捨。

不同寫法的範例

-- UNION: 重複的會被排除
SELECT 'A'
UNION
SELECT 'A';     -- 結果：只有一列 'A'

-- UNION ALL: 重複的也會留下來
SELECT 'A'
UNION ALL
SELECT 'A';     -- 結果：兩列 'A'

在遞迴查詢裡，為了不漏掉重要步驟，幾乎都用 UNION ALL 比較安全。

來看個常見的例子：我們有一張員工表，欄位有 employee_id、manager_id 跟 name。要從沒有主管的人（manager_id = NULL）開始，建出整個階層。

假設我們有這張員工表：employees

我們要搞清楚誰是誰的下屬，還有每個人在結構裡的層級。這很方便，像是你要在介面上畫員工樹，或是做組織報表時。

WITH RECURSIVE employee_hierarchy AS (
    -- 從沒有主管的人開始
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 加入下屬，層級加一
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

結果會像這樣：

這個查詢很直觀地展示怎麼「走」過員工的階層——從老闆到最基層。level 這個欄位很適合拿來排版或畫樹狀圖。

範例：商品分類

現在想像我們有一張商品分類表，每個分類可以有子分類，子分類又可以有自己的子分類。那要怎麼建出分類樹？

表格 categories

遞迴查詢：

WITH RECURSIVE category_tree AS (
    -- 基礎情況：找出根分類
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 遞迴部分：找出目前分類的子分類
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

結果：

現在我們就能看到有層級的分類樹啦。

為什麼遞迴 CTE 超酷？

遞迴 CTE 是 SQL 裡最強大、最有表現力的工具之一。你不用寫一堆巢狀邏輯，只要說明「從哪開始」（基礎情況）、「怎麼往下走」（遞迴部分）——剩下的 PostgreSQL 幫你搞定。

最常見的用途就是走階層：員工、商品分類、磁碟目錄、社群網路的圖。它很容易擴充：只要表裡有新資料，查詢就會自動抓到。超方便、超有彈性。

但也有地雷。一定要注意結束條件，不然查詢會無限 loop。還有記得加 index：大表沒 index 的遞迴查詢會超慢。UNION ALL 幾乎都是最佳選擇，特別是階層結構，不然你可能會因為去重而漏掉遞迴步驟。

調整好的遞迴 CTE，能讓你用幾行 SQL 就搞定複雜的商業邏輯——不用寫程序、迴圈或額外的 code。這就是 SQL 又正確又漂亮的時候！

用遞迴 CTE 常見的錯誤

• 無限遞迴：如果你沒設好結束條件（WHERE），查詢會一直跑下去。
• 資料太多：UNION ALL 用錯會讓重複資料爆多。
• 效能問題：遞迴查詢在大資料量下會很重。記得在關鍵欄位（像 manager_id）加 index，查詢會快很多。

什麼時候一定要用遞迴查詢？

有時候你會覺得遞迴查詢只是理論，但其實日常開發常常會遇到。比如：

• 要做公司結構或商品分類的報表；
• 要走訪資料夾樹，列出所有子目錄；
• 要分析圖——像社交關係、路徑、任務依賴；
• 或是單純想把複雜的關聯用好懂的方式展現出來。

只要你要走一個「一個東西依賴另一個東西」的結構，幾乎都會用到 WITH RECURSIVE。