SQL遞歸查詢實現組織機構樹三層級聯展示：從縣區溯源到省市國家

業務場景

組織機構表包含code（編碼）、parent（父節點）、level（層級）三字段，數據呈樹狀結構。需求：查詢所有層級為「縣/區」的記錄，輸出其對應的省級、國家級編碼，形成三列扁平化結果。

數據樣本

期望輸出

解決方案：自連接查詢

SELECT 
    one.code AS code1,
    two.parent AS code2,
    '中國' AS code3
FROM city one
INNER JOIN city two ON one.parent = two.code
WHERE one.level LIKE '%縣%';

原理解析

• 表別名技巧：同一張表取別名one（子節點）和two（父節點）
• 自連接條件：one.parent = two.code建立父子關係
• 層級過濾：one.level LIKE '%縣%'定位最底層節點
• 向上溯源：two.parent直接獲取父節點的父節點（即省級的父節點為國家）

進階擴展：通用遞歸CTE方案

若層級不固定或需動態深度，標準SQL遞歸CTE更通用：

WITH RECURSIVE org_tree AS (
    SELECT code, parent, level, 1 AS depth, code AS root_code
    FROM city WHERE parent = '0'
    UNION ALL
    SELECT c.code, c.parent, c.level, ot.depth + 1, ot.root_code
    FROM city c JOIN org_tree ot ON c.parent = ot.code
)
SELECT 
    leaf.code AS code1,
    parent.code AS code2,
    root.code AS code3
FROM org_tree leaf
JOIN org_tree parent ON leaf.parent = parent.code
JOIN org_tree root ON parent.parent = root.code
WHERE leaf.level LIKE '%縣%';

注意事項

• MySQL 8.0+、PostgreSQL、SQL Server、Oracle均支援CTE
• 大數據量時需在parent、code建立索引
• 生產環境建議用level = '縣/區'精確匹配而非模糊查詢

總結

固定三層結構用自連接最簡潔高效；不定深度樹形結構需遞歸CTE。理解表自連接是掌握SQL樹形查詢的關鍵一步。