Oracle SQL 進階技巧：DECODE 枚舉轉換、遞迴字典匯總、多層級編碼關聯查詢實戰

場景一：DECODE 實現枚舉值轉換（單表統計）

SELECT COUNT(t.id) AS amount,
       DECODE(t.CAPITAL_TYPE,
              1, '有帳設備',
              2, '買斷設備',
              3, '帳消庫存',
              4, '淨值為零') AS name
FROM Mach_Basic_Info t
WHERE t.Mach_state IN (1, 2)
GROUP BY t.CAPITAL_TYPE
ORDER BY amount DESC;

優化提示：12c+ 可用 CASE WHEN 替代 DECODE，語法更標準、可讀性更高；若枚舉值固定，建議建立字典表 CAPITAL_TYPE_DICT 以 JOIN 替代，利於維護與索引。

場景二：遞迴字典第一層匯總（ID-PARENT_ID 結構）

SELECT t1.parent_id, t1.amount, t2.type_name
FROM (
  SELECT d.parent_id, COUNT(t.id) AS amount
  FROM Mach_Basic_Info t
  JOIN Mach_Type_dict d ON t.Mach_type_id = d.id
  WHERE t.Mach_state IN (1, 2)
  GROUP BY d.parent_id
) t1
JOIN Mach_Type_dict t2 ON t1.parent_id = t2.id
ORDER BY t1.amount DESC;

適用於「大分類統計」，如設備大類、部門群組等。若需多層遞迴至根節點，11gR2+ 使用 CONNECT BY 或 WITH RECURSIVE（12c+）。

場景三：三層編碼字典關聯查詢（INNER_CODE / OUTER_CODE）

SELECT *
FROM (
  SELECT m.MANAGE_DEPT_ID,
         t.inner_code,      -- 內部層次編碼，開發可見
         m.original_value,
         m.Net_value
  FROM mach_basic_info m
  JOIN Mach_type_dict t ON m.Mach_type_id = t.id
  WHERE m.Mach_state IN (1, 2)
  -- 可在此處加入 inner_code/outer_code 篩選條件
);

inner_code 通常編碼規則如 1.01.003 代表「一級.二級.三級」，可用 SUBSTR/INSTR 或 REGEXP_LIKE 做層級篩選；outer_code 為業務給用戶看的代碼，查詢介面常用此欄位模糊比對。

效能優化檢查清單

• [ ] Mach_Basic_Info.Mach_type_id、Mach_Type_dict.id、parent_id 建立索引
• [ ] 統計資訊最新（DBMS_STATS.GATHER_TABLE_STATS）
• [ ] 大表分區按 MANAGE_DEPT_ID 或 Mach_state 分區
• [ ] 避免 SELECT *，明確列出所需欄位
• [ ] 考慮物化視圖預先聚合第一層統計，儀表板查詢秒級回應