用遞迴 CTE 處理階層結構的範例

想像一下：你有個網路商店，裡面有成千上萬的商品，全部都分門別類放好——分類、子分類、子子分類。在網站上看起來就是個很美的下拉選單，但在資料庫裡就變成一場惡夢。要怎麼用一條查詢把「電子產品 → 智慧型手機 → 配件」這整條分支抓出來？要怎麼算每個分類有幾層巢狀？一般的 JOIN 完全沒轍——這時就要用遞迴啦！

用遞迴 CTE 建商品分類結構

在關聯式資料庫裡，處理階層結構是經典問題之一。想像你有一棵商品分類樹：主分類、子分類、子子分類，依此類推。舉例來說：

電子產品
  └── 智慧型手機
      └── 配件
  └── 筆記型電腦
      └── 電競
  └── 攝影與錄影

這種結構在網店介面很好呈現，但要怎麼存在資料庫、又怎麼查出來？這時遞迴 CTE 就派上用場啦！

分類的原始資料表

首先我們來建一個 categories 資料表，專門存商品分類的資料：

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- 分類的唯一識別碼
    category_name TEXT NOT NULL,          -- 分類名稱
    parent_category_id INT                -- 上層分類（主分類是 NULL）
);

我們會在表裡加上這些資料：

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('電子產品', NULL),
    ('智慧型手機', 1),
    ('配件', 2),
    ('筆記型電腦', 1),
    ('電競', 4),
    ('攝影與錄影', 1);

這裡發生了什麼事：

• 電子產品 — 這是主分類（沒有上層，parent_category_id = NULL）。
• 智慧型手機 在 電子產品 裡面。
• 配件 屬於 智慧型手機。
• 其他分類也是類似。

現在 categories 資料表的結構長這樣：

用遞迴 CTE 建分類樹

現在我們想要查出所有分類的階層，還要標出每一層的深度。這時就要用遞迴 CTE 了。

WITH RECURSIVE category_tree AS (
    -- 基本查詢：選出所有根分類（parent_category_id = NULL）
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- 第一層
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- 遞迴查詢：找每個分類的子分類
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- 深度加一
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- 最後查詢：從 CTE 抓結果
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

查詢結果：

這裡發生什麼事？

1. 一開始基本查詢（SELECT … FROM categories WHERE parent_category_id IS NULL）會選出主分類。這裡只有 電子產品，depth = 1。
2. 接著遞迴查詢用 INNER JOIN 把子分類加進來，深度加一（depth + 1）。
3. 這個流程會一直跑，直到所有層級的子分類都找完。

實用小改進

這個基本範例可以用，但實際專案常常會需要更多功能。比如你要做麵包屑導航，或是想讓經理知道哪個分類底下有最多子分類。我們來看幾個實用的查詢進階技巧。

1. 加上完整分類路徑

有時候顯示完整分類路徑很有用，比如：電子產品 > 智慧型手機 > 配件。這可以用字串聚合來做：

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- 字串串接
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

查詢結果：

現在每個分類都有完整的巢狀路徑啦。

2. 計算子分類數量

如果我們想知道每個分類底下有幾個子分類怎麼辦？

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

查詢結果：

這張表顯示 電子產品 有 3 個子分類（智慧型手機、筆記型電腦、攝影與錄影），而 智慧型手機 跟 筆記型電腦 各有一個。

用遞迴 CTE 時的注意事項與常見錯誤

無限遞迴： 如果資料有循環（例如某個分類指向自己），查詢會進入無限迴圈。要避免這種情況，可以用 WHERE depth < N 或加上限制。

效能優化： 遞迴 CTE 在資料量大時會變慢。記得在 parent_category_id 上加索引會快很多。

用 UNION 取代 UNION ALL 的錯誤： 遞迴 CTE 一定要用 UNION ALL，不然 PostgreSQL 會幫你去重，查詢會變超慢。

這個範例展示了遞迴 CTE 怎麼幫你處理階層結構。學會從資料庫抓出階層資料，對你未來做網站選單、組織結構分析、甚至處理圖形資料都超有幫助。現在你已經準備好挑戰各種難題啦！

1

問卷/小測驗

CTE 入門，等級 27，課堂 4

未開放

CTE 入門

CTE 入門

開啟