Oracle增强型分组函数

lfjigu

　　ROLLUP()函数是对于GROUP BY 分组统计的功能扩展，可以实现分组统计求和的效果。
　　下面我们开始准备我们的试验的环境
　　--创建新表employee_salary，存储数据来自用户hr.employees
　　SQL>CREATE TABLE employee_salary ASSELECT E.FIRST_NAME,E.JOB_ID,E.MANAGER_ID,E.SALARY FROM HR.EMPLOYEES E WHERE E.JOB_ID='IT_PROG';
　　--查看新创建的表
　　SQL>SELECT * FROM employee_salary;
　　--显示效果如下
　　FIRST_NAME    JOB_ID      MANAGER_ID     SALARY
　　-------------------- ---------- --------------------------------
　　Alexander       IT_PROG        102         9000.00
　　Bruce             IT_PROG        103          6000.00
　　David             IT_PROG        103          4800.00
　　Valli               IT_PROG        103          4800.00
　　Diana             IT_PROG        103         4200.00
　　--首先按照JOB_ID进行分组，查看salary和
　　SQL>SELECT sa.job_id,SUM(sa.salary) FROM employee_salary sa GROUP BY sa.job_id;
　　--显示效果如下
　　JOB_ID      SUM(SA.SALARY)
　　---------- -----------------------
　　IT_PROG          28800
　　--按照MANAGER_ID进行分组，查看salary和
　　SELECT sa.manager_id,SUM(sa.salary) FROM employee_salary sa GROUP BY sa.manager_id;
　　--显示效果如下
　　MANAGER_ID    SUM(SA.SALARY)
　　---------- ----------------------------
　　102                      9000
　　103                    19800
　　--我们使用ROLLUP函数看看是什么效果
　　SELECT sa.job_id,sa.manager_id,SUM(sa.salary) FROM employee_salary sa GROUP BY ROLLUP(sa.job_id,sa.manager_id);
　　--显示效果如下
　　JOB_ID     MANAGER_ID  SUM(SA.SALARY)
　　---------- ---------- --------------------------------
　　IT_PROG       102           9000
　　IT_PROG       103          19800
　　IT_PROG                      28800
　　28800
　　说明：ROLLUP解析过程，以ROLLUP(a,b)为例
　　ROLLUP(a,b)== GROUP(a,b) UNION ALL GROUP(a) UNIONALL GROUP()
　　即：解析顺序是从右至左，显示按照a,b分组，接下来是按照a分组，最后是对全表分组；
　　上面ROLLUP (sa.job_id,sa.manager_id)等同下面UNION ALL的集合操作
　　SELECT sa.job_id,sa.manager_id,SUM(sa.salary) FROM employee_salary sa
　　GROUP BY sa.job_id,sa.manager_id
　　UNION ALL
　　SELECT sa.job_id,NULL,SUM(sa.salary) FROM employee_salary sa
　　GROUP BY sa.job_id
　　UNION ALL
　　SELECT NULL,NULL,SUM(sa.salary) FROM employee_salary sa GROUP BY()
　　ORDER BY 1,2;
　　--显示效果如下
　　JOB_ID     MANAGER_ID  SUM(SA.SALARY)
　　---------- ---------- --------------------------------
　　IT_PROG       102                9000
　　IT_PROG       103               19800
　　IT_PROG                            28800
　　28800
　　说明：虽然最后展示的效果是相同的，但是ROLLUP()函数的执行效率要比UNION ALL的效率要高、要快。
　　ROLLUP(A,B,C)是在执行组合操作，无顺序，组合公式是（n+1），当n=3时，组合结果就是有4个。
　　ROLLUP()中的参数位置不同，得出的结果可能不一样！
　　在理解ROLLUP的基础上再来理解CUBE()就比较容易，ROLLUP()在执行组合操作，CUBE()就是在执行排序动作，从左至右，排序公式是2N次方。
　　CUBE(A,B,C)==GROUP BY (A,B,C) UNION ALL GROUP BY (A,B) UNION ALL GROUP BY (A,C) UNION ALL GROUPBY (A) UNION ALL GROUP BY (B) UNION ALL GROUP BY (C) UNION ALL GROUP BY ()
　　例如：执行下列语句
　　SQL> SELECT sa.job_id,sa.manager_id,SUM(sa.salary) FROM employee_salary sa GROUP BY CUBE(sa.job_id,sa.manager_id);
　　--显示效果如下
　　JOB_ID    MANAGER_ID SUM(SA.SALARY)
　　---------- ---------- -----------------------------
　　28800
　　102                   9000
　　103                 19800
　　IT_PROG                              28800
　　IT_PROG        102                   9000
　　IT_PROG        103                  19800
　　6 rows selected
　　上面的语句等同执行下列语句
　　SQL>SELECT sa.job_id,sa.manager_id,SUM(sa.salary) FROM employee_salary sa GROUP BY (sa.job_id,sa.manager_id)
　　UNION ALL
　　SELECT sa.job_id,NULL,SUM(sa.salary) FROM employee_salary sa
　　GROUP BY (sa.job_id)
　　UNION ALL
　　SELECT NULL,sa.manager_id,SUM(sa.salary) FROM employee_salary sa
　　GROUP BY (sa.manager_id)
　　UNION ALL
　　SELECT NULL,NULL,SUM(sa.salary) FROM employee_salary sa
　　GROUPBY ();
　　关于GROUPING
　　GROUPING(A)用于判断对于分组后的列是否是空值NULL，返回值有0和1两个值，1表示，该列为空——NULL，这个NULL值是因为分组时产生，否则则为0；