萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 階層查詢實現組織架構薪資彙總 (CONNECT_BY_ROOT)

組織結構儲存於單表 (員工 ID、主管 ID),層級動態變化。需統計「某員工及其所有下屬薪資總和」,CEO 即全公司總薪資。Oracle CONNECT_BY_ROOT 運算子完美解決。

核心語法

SELECT CONNECT_BY_ROOT last_name AS root_name, salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id
START WITH last_name = 'King';  -- 可選,指定根節點
  • CONNECT_BY_ROOT col:回傳根節點 (START WITH 指定或隱含) 的 col 值
  • 配合 GROUP BY CONNECT_BY_ROOT col 即可彙總各根節點下的度量

完整範例:各主管含下屬總薪資

SELECT name, SUM(salary) AS 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;

輸出結果

NAME       TOTAL_SALARY
---------- ------------
Gietz              8300
Higgins           20300
King              20300
Kochhar           20300

King (CEO) 總薪資 20300 = 部門全員薪資和;Higgins/Kochhar 為中層主管,含各自下屬。

進階應用:路徑顯示

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";

輸出每位員工到各層級主管的路徑,便於審計與視覺化。

注意事項

  • 避免循環參考:CONNECT BY NOCYCLE PRIOR ... (10g+)
  • 大量資料時考慮物化視圖預先彙總
  • START WITH 省略時,Oracle 以每個無父節點的行作為根節點各自遍歷
copyright © 萬盛學電腦網 all rights reserved