對于一般的數(shù)據(jù)模型來說，一般是有一個事實表，若干個維度表，通過事實表與維度表的連接，實現(xiàn)不同層次的查詢匯總。

創(chuàng)新互聯(lián)公司網(wǎng)站建設(shè)服務(wù)商，為中小企業(yè)提供成都做網(wǎng)站、成都網(wǎng)站建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)服務(wù)，網(wǎng)站設(shè)計，網(wǎng)站托管等一站式綜合服務(wù)型公司，專業(yè)打造企業(yè)形象網(wǎng)站，讓您在眾多競爭對手中脫穎而出創(chuàng)新互聯(lián)公司。

問題是對于組織結(jié)構(gòu)而言，一般所有的數(shù)據(jù)都存貯于一個表中，而且，組織結(jié)構(gòu)的層次也是動態(tài)的。那么，在這種情況下，如何實現(xiàn)員工工資的匯總呢？一個比較有趣的問題是: 如何統(tǒng)計員工及其所有被管理員工的總工資，舉個例子，CEO的總工資就是整個公司總有員工的總工資之和，包括他自己。

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

下面是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 employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id
   ORDER BY "Employee", "Manager", "Pathlen", "Path";

Employee        Manager            Pathlen Path
--------------- --------------- ---------- ------------------------------
Gietz           Higgins                  1 /Higgins/Gietz
Gietz           King                     3 /King/Kochhar/Higgins/Gietz
Gietz           Kochhar                  2 /Kochhar/Higgins/Gietz
Higgins         King                     2 /King/Kochhar/Higgins
Higgins         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, 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