如何用Oracle實現組織結構中的匯總統計

對於一般的數據模型來說，一般是有一個事實表，若干個維度表，通過事實表與維度表的連接，實現不同層次的查詢匯總。

問題是對於組織結構而言，一般所有的數據都存貯於一個表中，而且，組織結構的層次也是動態的。那麼，在這種情況下，如何實現員工工資的匯總呢？一個比較有趣的問題是: 如何統計員工及其所有被管理員工的總工資，舉個例子，CEO的總工資就是整個公司總有員工的總工資之和，包括他自己。

Oracle引入了一個擴展的運算符，專門用來處理此種情形，它就是connect_by_root。當以connect_by_root修飾一個列名時，Oracle將返回根節點對應的此列的值。例如，當start with 為 last_name = ‘King’時，這時返回的所有行的connect_by_root last_name的值都將為’King’。這時，對所有行的累計就是對’King’的數據的累計了。當不指定start with 子句時，Oracle將對每個節點依次進行遍歷，於是，我們可以對返回的結果對last_name進行一次group by，那麼我們就得到了所有last_name對應的匯總工資了。

下面是Oracle文檔中的例子。

The following example returns the last name of each employee in department 110, each manager

above that employee in the hierarchy, the number of levels between manager and employee,

and the path between the two:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"FROM employeesWHERE LEVEL > 1 and department_id = 110CONNECT BY PRIOR employee_id = manager_idORDER BY "Employee", "Manager", "Pathlen", "Path";

Employee　　　　　　　 Manager　　　　　　　　　　　 Pathlen Path--------------- --------------- ---------- ------------------------------Gietz　　　　　　　　　　 Higgins　　　　　　　　　　　　　　　　　 1 /Higgins/GietzGietz　　　　　　　　　　 King　　　　　　　　　　　　　　　　　　　　 3 /King/Kochhar/Higgins/GietzGietz　　　　　　　　　　 Kochhar　　　　　　　　　　　　　　　　　 2 /Kochhar/Higgins/GietzHiggins　　　　　　　　 King　　　　　　　　　　　　　　　　　　　　 2 /King/Kochhar/HigginsHiggins　　　　　　　　 Kochhar　　　　　　　　　　　　　　　　　 1 /Kochhar/HigginsThe following example uses a GROUP BY clause to return the total salary of each employee in department 110 and all employees below that employee in the hierarchy:

SELECT name, SUM(salary) "Total_Salary" FROM (SELECT CONNECT_BY_ROOT last_name as name, SalaryFROM employeesWHERE department_id = 110CONNECT BY PRIOR employee_id = manager_id)GROUP BY nameORDER BY name, "Total_Salary";
NAME　　　　　　　　　　　　　　　　　　　　　 Total_Salary------------------------- ------------Gietz　　　　　　　　　　　　　　　　　　　　　　　　　　　　 8300Higgins　　　　　　　　　　　　　　　　　　　　　　　　　 20300King　　　　　　　　　　　　　　　　　　　　　　　　　　　　 20300Kochhar　　　　　　　　　　　　　　　　　　　　　　　　　 20300