Oracle 階層查詢實現組織架構薪資匯總：CONNECT_BY_ROOT 與 SYS_CONNECT_BY_PATH 進階應用

業務場景：動態組織樹的遞歸匯總

員工表自關聯 (EMPLOYEE_ID / MANAGER_ID)，層級不固定。需求：統計每位主管「本人 + 所有下屬 (遞歸)」的薪資總和。CEO 總和 = 全公司薪資。

核心函數：CONNECT_BY_ROOT

語法：CONNECT_BY_ROOT column_name

• 返回當前行在階層樹中的根節點對應欄位值。
• 搭配 START WITH 指定根節點時，所有行返回同一根值。
• 不指定 START WITH 時，Oracle 依序以每個節點為根進行遍歷 → 配合 GROUP BY root_column 即得各節點匯總。

官方範例深度解析

範例 1：顯示員工、所有上級主管、層級深度與路徑

SELECT last_name "Employee",
       CONNECT_BY_ROOT last_name "Manager",
       LEVEL-1 "Pathlen",
       SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL > 1 AND department_id = 110
CONNECT BY PRIOR employee_id = manager_id
ORDER BY "Employee", "Manager", "Pathlen", "Path";

結果：每位員工對應其所有上級 (含間接)，路徑如 /King/Kochhar/Higgins/Gietz。

範例 2：各主管含下屬薪資總和 (核心需求)

SELECT name, SUM(salary) "Total_Salary"
FROM (
  SELECT CONNECT_BY_ROOT last_name AS name, salary
  FROM employees
  WHERE department_id = 110
  CONNECT BY PRIOR employee_id = manager_id
)
GROUP BY name
ORDER BY name, "Total_Salary";

結果：

解讀： King/Kochhar/Higgins 總和相同 (20300) = 部門 110 全員薪資；Gietz 為葉節點僅含自身薪資。

關鍵語法組合拆解

擴展應用場景

• 組織架構下的預算分銷、權限繼承、KPI 滾動彙總。
• 物料清單 (BOM) 成本遞歸計算。
• 地理區域銷售層級彙總 (國 → 省 → 市 → 區)。
• 搭配 CONNECT_BY_ISLEAF 識別葉節點、CONNECT_BY_ISCYCLE 偵測循環引用。

效能提示

• 大型階層表建議在 MANAGER_ID 上建立索引。
• 避免在階層查詢中使用成本高的 PL/SQL 函數。
• 超大樹結構考慮物化視圖預計算或 DBMS_HIERARCHY 維護。