Oracle 階層式查詢實戰：行政區劃三級聯動 (省/市/縣) 完整實現

行政區劃資料天然具有樹狀結構：省 (代碼後 4 位為 0000) → 市 (後 2 位為 00) → 縣/區。本文示範如何利用 Oracle CONNECT BY 階層查詢，從扁平化代碼表衍生父子關係欄位，實現三級聯動查詢與層級標記。

資料樣本 (DM_XZQH 表)

DM        MC
230000    黑龍江省
230100    哈爾濱市
230101    市轄區
230102    道裡區
230103    南崗區
232700    大興安嶺地區
230123    依蘭縣
230182    雙城市
...

步驟一：衍生父級代碼欄位 (SJBM)

利用代碼規則推導上級行政單位代碼：

SELECT t.dm, t.mc,
       CASE
         WHEN SUBSTR(t.dm, 3) = '0000' THEN 1                    -- 省級：虛擬根節點標記為 1
         WHEN SUBSTR(t.dm, 5) = '00' THEN TO_NUMBER(SUBSTR(t.dm,1,2) || '0000')  -- 市級：父級為省代碼
         WHEN SUBSTR(t.dm, 5) != '00' THEN TO_NUMBER(SUBSTR(t.dm,1,4) || '00')   -- 縣級：父級為市代碼
         ELSE 0
       END AS sjbm
FROM dm_xzqh t;

結果片段：

步驟二：階層查詢輸出完整樹狀結構

SELECT LEVEL, dm, mc, sjbm
FROM (
  SELECT t.dm, t.mc,
         CASE
           WHEN SUBSTR(t.dm, 3) = '0000' THEN 1
           WHEN SUBSTR(t.dm, 5) = '00' THEN TO_NUMBER(SUBSTR(t.dm,1,2) || '0000')
           WHEN SUBSTR(t.dm, 5) != '00' THEN TO_NUMBER(SUBSTR(t.dm,1,4) || '00')
         END AS sjbm
  FROM dm_xzqh t
)
START WITH sjbm = 1                 -- 根節點：省級 (虛擬父級=1)
CONNECT BY PRIOR dm = sjbm          -- 遞迴條件：父節點 DM = 子節點 SJBM
ORDER SIBLINGS BY dm;               -- 同層按代碼排序

關鍵字句解析

• LEVEL：偽欄位，根節點為 1，每遞迴一層 +1。
• START WITH：指定根節點過濾條件。
• CONNECT BY PRIOR 父欄位 = 子欄位：定義父子關係遞迴方向。
• ORDER SIBLINGS BY：同層節點排序，**不影響層次結構**。
• CONNECT_BY_ISLEAF：判斷是否葉子節點 (1=葉子)。
• SYS_CONNECT_BY_PATH(col, '/')：獲取從根到當前節點的路徑字串。

進階應用：按層級篩選與路徑顯示

-- 僅查詢市級 (LEVEL=2)
SELECT dm, mc FROM (...) WHERE LEVEL = 2;

-- 顯示完整層級路徑 (如: 黑龍江省/哈爾濱市/道裡區)
SELECT LEVEL,
       SYS_CONNECT_BY_PATH(mc, '/') AS full_path
FROM (...)
START WITH sjbm = 1
CONNECT BY PRIOR dm = sjbm;

-- 統計各省下轄市/縣數量
SELECT p.mc AS province, COUNT(c.dm) AS sub_count
FROM dm_xzqh p
JOIN dm_xzqh c ON c.sjbm = p.dm
WHERE p.sjbm = 1
GROUP BY p.mc;

效能優化建議

1. 索引： 在 DM (PK) 與 SJBM (FK) 上建立索引，加速遞迴連接。
2. 材化視圖： 行政區劃變動極低頻，建議建立材化視圖預計算層級、路徑、完整樹狀 JSON/XML，前端直接讀取。
3. 避免 N+1 查詢： 前端三級聯動下拉框，**一次性查詢全量樹狀資料** (含 LEVEL, SJBM, PATH)，前端記憶體中按 SJBM 過濾渲染，避免每級發起 AJAX 請求。

常見陷阱

• 循環引用： 若資料髒導致 CONNECT BY 陷入無限循環，Oracle 拋 ORA-01436。可加 NOCYCLE 關鍵字 (10g+) 忽略循環分支：CONNECT BY NOCYCLE PRIOR dm = sjbm。
• 虛擬根節點： 省級 SJBM=1 為人工約定，實際表中無此代碼，**務必確保 START WITH 條件精確匹配**。
• LEVEL 從 1 開始，不可用於 WHERE 子句過濾 (需包裝子查詢或用 CONNECT_BY_ROOT)。