Oracle Group by語句和遞歸查詢總結

一、Group By Rollup的使用方法

1、概念

   在Group By 中使用Rollup産生正常分組彙總行以及分組小計： 

2、舉例

SELECT department_id
      ,job_id
      ,SUM(salary)
   FROM employees
   WHERE department_id < 60
   GROUP BY ROLLUP(department_id, job_id);
           

   說明：首先按照department_id, job_id進行分組，然後按照department_id進行分組，最後無條件分組。

3、總結

   Rollup 後面跟了n個字段，就将進行n+1次分組，從右到左每次減少一個字段進行分組；然後進行 union（連接配接）

二、Group By Cube的使用方法

1、概念

   在Group By中使用Cube産生Rollup結果集 + 多元度的交叉表資料源：

2、舉例

SELECT   department_id, job_id, SUM(salary) 
   FROM     employees   
   WHERE    department_id < 60
   GROUP BY CUBE (department_id, job_id) ;
           

   說明：首先按照department_id, job_id進行分組，然後按照department_id進行分組，再按照job_id進行分組，最後無條件分組。

3、總結

   Cube 後面跟了 後面跟了n個字段，就将進行2的N次方的分組運算，然後進行； 

   就是把跟着CUBE後面的分組條件進行交叉分組，然後進行無條件分組。  

三、GROUPING函數的使用方法

1、概念

   GROUPING函數：Rollup 和 Cube有點抽象，他分别相當于n+1 和 2的n次方正常 Group by 運算；

   那麼在Rollup 和 Cube的結果集中如何很明确的看出哪些行是針對那些列或者列的組合進行分組運算的結果的？

   答案是可以使用Grouping 函數；沒有被Grouping到傳回1，否則傳回0 

2、舉例

 SELECT department_id DEPTID, job_id JOB,  
         SUM(salary), 
         GROUPING(department_id) GRP_DEPT, 
         GROUPING(job_id) GRP_JOB 
   FROM     employees 
   WHERE    department_id < 50 
   GROUP BY ROLLUP(department_id, job_id); 
           

   可以通過grouping來檢視使用了誰來分組

四、Grouping Set的使用方法

1、概念

   使用Grouping Set 來代替多次UNION:

2、舉例

SELECT   department_id, job_id,  
            manager_id,avg(salary) 
   FROM     employees 
   GROUP BY GROUPING SETS  
           ((department_id,job_id), (job_id,manager_id));
           

   說明：這裡使用了GROUPING SETS将兩個分組條件查詢的結果union起來。

   上面語句相當于：

SELECT department_id
      ,job_id
      ,null manager_id
      ,AVG(salary)
     FROM employees
  GROUP BY department_id, job_id 
    union 
   SELECT null department_id
      ,job_id
      ,manager_id
      ,AVG(salary)
    FROM employees
   GROUP BY job_id, manager_id;
           

遞歸查詢總結

一、start with...connect by prior...使用

1、舉例

   eg1：

SELECT last_name || ' reports to ' || PRIOR last_name "Walk Top Down"
     FROM employees
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id;
           

   說明：這句語句用來顯示查詢從King開始，從上往下的各級員工。

   eg2：

 SELECT employee_id
      ,last_name
      ,job_id
      ,manager_id
    FROM employees
   START WITH employee_id = 101
   CONNECT BY PRIOR manager_id = employee_id;
           

   說明：這句語句用來查詢從101開始，從下往上的各級員工。

2、總結

   start with 從誰開始

   connect by prion 後跟遞歸條件

   prior跟誰就代表把...當做下級（上級）的...   

二、使用LEVEL關鍵字和LPAD函數，在OUTPUT中顯示樹形層次。

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart 
FROM   employees 
START WITH last_name='King'  
CONNECT BY PRIOR employee_id=manager_id