ORACLE中扩展的group by-liord府邸

刘伟

　　扩展的GROUP BY
　　所谓的扩展的GROUP BY就是使用了分析函数rollup() 和 cube()。
　　rollup()：使分组结果中包含小计及总计信息，并可以传入多列字段
　　cube()：对做为参数传入的每一列都进行小计，多列的时候能显示优势。
　　例：

　　select>　　ID SUM(MOUNT)
　　---------- ----------
　　1         60
　　2        150
　　3        240
　　4        210
　　5        440
　　1100

　　select>　　ID SUM(MOUNT)
　　---------- ----------
　　1100
　　1         60
　　2        150
　　3        240
　　4        210
　　5        440
　　或者,用nulls 显示指定空值的首尾位置:

　　select>　　注释:这里必须要有order by 才能使用nulls last，而且如果group by后面是id的时候nulls last/first 才有效果，如果是sum(mount)则没有效果.

　　SQL> select>　　ID SUM(MOUNT)
　　---------- ----------
　　1100
　　5        440
　　4        210
　　3        240
　　2        150
　　1         60

　　SQL> select>　　ID SUM(MOUNT)
　　---------- ----------
　　5        440
　　4        210
　　3        240
　　2        150
　　1         60
　　1100

　　SQL> select>　　ID SUM(MOUNT)
　　---------- ----------
　　1100
　　5        440
　　3        240
　　4        210
　　2        150
　　1         60
　　下面是一些与rollup、cube联合使用的函数：
　　groupping():只能在有rollup或者cube的语句中使用，判断当前行是否是小计或者 总计行（实质是通过是否列为空来判断）。比如，小计行的name列为空（即小计行），则返回1，不为空则返回0；

　　SQL> select>　　ID SUM(MOUNT) GROUPING(ID)
　　---------- ---------- ------------
　　1100            1
　　5        440            0
　　4        210            0
　　3        240            0
　　2        150            0
　　1         60            0

　　SQL> select case grouping(id) when 1 then 'zj' else>　　ID   SUM(MOUNT)
　　---- ----------
　　zj         1100
　　5           440
　　4           210
　　3           240
　　2           150
　　1            60
　　grouping_id(col1,col2)计算位向量，如例子中deptno和job在grouping的时候都返回1，则 grouping_id(deptno,job)结果则为二进制11，即3。job为空，返回0，而deptno返回1，则10，即2; （可以多列）
　　例：
　　SQL> select deptno,job,sum(sal) ,grouping(deptno) a,grouping(job) b,grouping_id(deptno,job) c from emp group by cube(deptno,job) order by deptno;
　　DEPTNO JOB         SUM(SAL)          A          B          C
　　------ --------- ---------- ---------- ---------- ----------
　　10 CLERK           1300          0          0          0
　　10 MANAGER         2450          0          0          0
　　10 PRESIDENT       5000          0          0          0
　　10                 8750          0          1          1
　　20 ANALYST         6000          0          0          0
　　20 CLERK           1900          0          0          0
　　20 MANAGER         2975          0          0          0
　　20                10875          0          1          1
　　30 CLERK            950          0          0          0
　　30 MANAGER         2850          0          0          0
　　30 SALESMAN        5600          0          0          0
　　30                 9400          0          1          1
　　ANALYST         6000          1          0          2
　　CLERK           4150          1          0          2
　　MANAGER         8275          1          0          2
　　PRESIDENT       5000          1          0          2
　　SALESMAN        5600          1          0          2
　　29025          1          1          3
　　它常与having联系使用，来筛选结果中的小计或总计的行。其实只需要grouping_id行大于零的都是。
　　SQL> select deptno,job,sum(sal) ,grouping(deptno) a,grouping(job) b,grouping_id(deptno,job) c from emp group by cube(deptno,job) having grouping_id(deptno,job)>0 order by deptno;
　　DEPTNO JOB         SUM(SAL)          A          B          C
　　------ --------- ---------- ---------- ---------- ----------
　　10                 8750          0          1          1
　　20                10875          0          1          1
　　30                 9400          0          1          1
　　ANALYST         6000          1          0          2
　　CLERK           4150          1          0          2
　　MANAGER         8275          1          0          2
　　PRESIDENT       5000          1          0          2
　　SALESMAN        5600          1          0          2
　　29025          1          1          3
　　注释：这样的结果只是比grouping sets子句多了一个最后的总计。
　　group_id()控制重复值，记录第一次出现时为0，第二次出现为1,第三次出现为2。
　　它不接受任何参数。
　　注释： group by语句可以同时使用普通列、rollup()和cube()等作为条件，如果一个列在不同地方出现了多次，结果集可能会重复。同时使用的含义是：普通列加上高级函数的每个结果作为group by的条件汇总，
　　SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
　　DEPTNO JOB         SUM(SAL)
　　------ --------- ----------
　　10 CLERK           1300
　　10 MANAGER         2450
　　10 PRESIDENT       5000
　　10                 8750
　　20 CLERK           1900
　　20 ANALYST         6000
　　20 MANAGER         2975
　　20                10875
　　30 CLERK            950
　　30 MANAGER         2850
　　30 SALESMAN        5600
　　30                 9400
　　29025
　　SQL> select deptno,job,sum(sal) from emp group by deptno,rollup(deptno,job);
　　DEPTNO JOB         SUM(SAL)
　　------ --------- ----------
　　10 CLERK           1300
　　10 MANAGER         2450
　　10 PRESIDENT       5000
　　20 CLERK           1900
　　20 ANALYST         6000
　　20 MANAGER         2975
　　30 CLERK            950
　　30 MANAGER         2850
　　30 SALESMAN        5600
　　10                 8750
　　20                10875
　　30                 9400
　　10                 8750
　　20                10875
　　30                 9400
　　SQL> select deptno,job,sum(sal) ,group_id()  from emp group by deptno,rollup(deptno,job);
　　DEPTNO JOB         SUM(SAL) GROUP_ID()
　　------ --------- ---------- ----------
　　10 CLERK           1300          0
　　10 MANAGER         2450          0
　　10 PRESIDENT       5000          0
　　20 CLERK           1900          0
　　20 ANALYST         6000          0
　　20 MANAGER         2975          0
　　30 CLERK            950          0
　　30 MANAGER         2850          0
　　30 SALESMAN        5600          0
　　10                 8750          0
　　20                10875          0
　　30                 9400          0
　　10                 8750          1
　　20                10875          1
　　30                 9400          1
　　SQL> select deptno,job,sum(sal) ,group_id()  from emp group by deptno,rollup(deptno,job) having group_id()=0;
　　DEPTNO JOB         SUM(SAL) GROUP_ID()
　　------ --------- ---------- ----------
　　10 CLERK           1300          0
　　10 MANAGER         2450          0
　　10 PRESIDENT       5000          0
　　20 CLERK           1900          0
　　20 ANALYST         6000          0
　　20 MANAGER         2975          0
　　30 CLERK            950          0
　　30 MANAGER         2850          0
　　30 SALESMAN        5600          0
　　10                 8750          0
　　20                10875          0
　　30                 9400          0
　　grouping sets子句：
　　group by后带grouping sets子句就是只返回按单个列分组后的统计数据，不返回多个列组合分组的统计数据。
　　例：Group by grouping sets(A ,B)
　　产生的分组种数：2种；
　　第一种：group by A
　　第二种：group by B
　　SQL> select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
　　DEPTNO JOB         SUM(SAL)
　　------ --------- ----------
　　10 CLERK           1300
　　10 MANAGER         2450
　　10 PRESIDENT       5000
　　20 ANALYST         6000
　　20 CLERK           1900
　　20 MANAGER         2975
　　30 CLERK            950
　　30 MANAGER         2850
　　30 SALESMAN        5600
　　SQL> select deptno,job,sum(sal) from emp group by grouping sets(deptno,job) order by deptno;
　　DEPTNO JOB         SUM(SAL)
　　------ --------- ----------
　　10                 8750
　　20                10875
　　30                 9400
　　ANALYST         6000
　　PRESIDENT       5000
　　SALESMAN        5600
　　CLERK           4150
　　MANAGER         8275