日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

對(duì)于一般的數(shù)據(jù)模型來說，一般是有一個(gè)事實(shí)表，若干個(gè)維度表，通過事實(shí)表與維度表的連接，實(shí)現(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è)計(jì)，網(wǎng)站托管等一站式綜合服務(wù)型公司，專業(yè)打造企業(yè)形象網(wǎng)站，讓您在眾多競爭對(duì)手中脫穎而出創(chuàng)新互聯(lián)公司。

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

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