Oracle Group by语句和递归查询总结

蔚落 2022-05-19 06:37 264阅读 0赞

**一、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