SQL 樹型結構轉扁平列：多次自連接與遞歸 CTE 兩種方案詳解

問題描述

將遞歸樹型表（如 `id, parent_id, name`）轉換為固定列的扁平結構（如 `level1, level2, level3, level4`），方便報表展示或導出。

方案一：多次 LEFT JOIN（層級固定、深度已知）

適用於最大深度固定且較淺（如 ≤ 5 級）的場景。

SELECT 
    t1.name AS level1,
    t2.name AS level2,
    t3.name AS level3,
    t4.name AS level4
FROM tree t1
LEFT JOIN tree t2 ON t2.parent_id = t1.id
LEFT JOIN tree t3 ON t3.parent_id = t2.id
LEFT JOIN tree t4 ON t4.parent_id = t3.id
WHERE t1.parent_id IS NULL;  -- 只從根節點開始

✅ 寫法直觀、執行計劃穩定
❌ 深度超過預期需修改 SQL、深度不固定時冗余列為 NULL

方案二：遞歸 CTE + 動態列生成（深度不固定）

適用於深度不固定，需配合應用層動態生成列或轉為 JSON/XML 輸出。

1. 遞歸 CTE 獲取全路徑

WITH RECURSIVE tree_path AS (
    SELECT id, parent_id, name, 1 AS lvl,
           CAST(name AS VARCHAR(MAX)) AS path
    FROM tree
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT t.id, t.parent_id, t.name, tp.lvl + 1,
           tp.path + ' > ' + t.name
    FROM tree t
    JOIN tree_path tp ON t.parent_id = tp.id
)
SELECT * FROM tree_path ORDER BY path;

2. 應用層轉扁平列

將 `path` 字符串按分隔符拆分，動態生成所需列數。或直接輸出 JSON：

SELECT id, parent_id, name, lvl, path
FROM tree_path
FOR JSON AUTO;  -- SQL Server / PostgreSQL

方案三：物化路徑 / 閉包表（高頻讀取場景）

若樹結構變動少、查詢極頻繁，預先計算存儲：

• 物化路徑：每行存 `path = '1/5/12/'`，查子樹 `LIKE '1/5/%'`
• 閉包表：單獨表存所有祖先後代關係 `(ancestor, descendant, depth)`

MySQL 8.0 / PostgreSQL / SQL Server 語法差異提示

選型建議

1. 深度 ≤ 4 且固定 → 多次 LEFT JOIN 最簡單
2. 深度不固定、需導出報表 → 遞歸 CTE + 應用層拆分 3. 高頻遞歸查詢（如權限樹、組織架構） → 物化路徑或閉包表 4. 需支援移動節點操作 → 閉包表維護成本最低

總結

樹轉扁平列無銀彈：固定深度用多次自連接最省事；不固定深度用遞歸 CTE 產出路徑再由應用層處理；極高性能需求預先物化。根據業務變更頻率、查詢模式、最大深度三因素選型。