oracle中group by的扩展

wxot3t77is

　　
　　
　　扩展了的group by子句.我们都知道，group by子句用于将查询结果分组。下面是未扩展的group by子句SQL> select job,sum(sal)2  from emp3  group by job;JOB         SUM(SAL)--------- ----------ANALYST         6000CLERK           4150MANAGER         8275PRESIDENT       5000SALESMAN        5600--扩展了的group by子句。1.rollup子句作用：为每一个分组返回一条小计录，并为全部分组返回总计。可以向rollup中传递一列或者多列，rollup可以结合聚合函数一起使用。实例1：select job,empno,sum(sal)from empgroup by rollup(job,empno);--JOB       EMPNO   SUM(SAL)--------- ----- ----------CLERK      7900        950CLERK      7369        800CLERK      7876       1100CLERK      7934       1300CLERK                 4150ANALYST    7788       3000ANALYST    7902       3000ANALYST               6000MANAGER    7566       2975MANAGER    7698       2850MANAGER    7782       2450MANAGER               8275SALESMAN   7499       1600SALESMAN   7521       1250SALESMAN   7654       1250SALESMAN   7844       1500SALESMAN              5600PRESIDENT  7839       5000PRESIDENT             500029025实例2：select job,empno,avg(sal)from empgroup by rollup(job,empno);--JOB       EMPNO   AVG(SAL)--------- ----- ----------CLERK      7900        950CLERK      7369        800CLERK      7876       1100CLERK      7934       1300CLERK               1037.5ANALYST    7788       3000ANALYST    7902       3000ANALYST               3000MANAGER    7566       2975MANAGER    7698       2850MANAGER    7782       2450MANAGER         2758.33333SALESMAN   7499       1600SALESMAN   7521       1250SALESMAN   7654       1250SALESMAN   7844       1500SALESMAN              1400PRESIDENT  7839       5000PRESIDENT             50002073.214282.cube子句作用：返回cube中所有列组合的小计信息，同时在最后显示总计信息。实例1：select job,sum(sal)from empgroup by cube(job);--JOB         SUM(SAL)--------- ----------29025CLERK           4150ANALYST         6000MANAGER         8275SALESMAN        5600PRESIDENT       5000实例2：select empno,job,avg(sal)from empgroup by cube(empno,job);--EMPNO JOB         AVG(SAL)----- --------- ----------2073.21428CLERK         1037.5ANALYST         3000MANAGER   2758.33333SALESMAN        1400PRESIDENT       50007900                  9507900 CLERK            9507369                  8007369 CLERK            8007499                 16007499 SALESMAN        16007521                 12507521 SALESMAN        12507566                 29757566 MANAGER         29757654                 12507654 SALESMAN        12507698                 28507698 MANAGER         28507782                 24507782 MANAGER         24507788                 30007788 ANALYST         30007839                 50007839 PRESIDENT       50007844                 15007844 SALESMAN        15007876                 11007876 CLERK           11007902                 30007902 ANALYST         30007934                 13007934 CLERK           13003.grouping sets子句作用：按分组列，分别只返回分组列小计记录实例：select empno,job,avg(sal)from empgroup by grouping sets(empno,job);--EMPNO JOB         AVG(SAL)----- --------- ----------7369                  8007499                 16007521                 12507566                 29757654                 12507698                 28507782                 24507788                 30007839                 50007844                 15007876                 11007900                  9507902                 30007934                 1300ANALYST         3000CLERK         1037.5MANAGER   2758.33333PRESIDENT       5000SALESMAN        1400--扩展group by子句的综合应用rollup子句和cube子句还可以结合其他oracle内置函数一起使用，得到你所想要的结果。grouping()函数:接受一列，若此列为空，返回1，若此列非空，返回0.实例1：在rollup中对单列使用grouping()select grouping(job),job,sum(sal)from empgroup by rollup(job);--GROUPING(JOB) JOB         SUM(SAL)------------- --------- ----------0 ANALYST         60000 CLERK           41500 MANAGER         82750 PRESIDENT       50000 SALESMAN        56001                29025--实例2：使用decode()转换grouping()的返回值select decode(grouping(job),1,'总计',job) divisions,sum(sal)from empgroup by rollup(job);DIVISIONS   SUM(SAL)--------- ----------ANALYST         6000CLERK           4150MANAGER         8275PRESIDENT       5000SALESMAN        5600总计           29025--实例3：使用decode()和grouping()转换多列select decode(grouping(job),1,'总计',job) job,decode(grouping(empno),1,'小计',empno) empno,avg(sal)from empgroup by rollup(job,empno);--JOB       EMPNO                                      AVG(SAL)--------- ---------------------------------------- ----------CLERK     7900                                            950CLERK     7369                                            800CLERK     7876                                           1100CLERK     7934                                           1300CLERK     小计                                         1037.5ANALYST   7788                                           3000ANALYST   7902                                           3000ANALYST   小计                                           3000MANAGER   7566                                           2975MANAGER   7698                                           2850MANAGER   7782                                           2450MANAGER   小计                                     2758.33333SALESMAN  7499                                           1600SALESMAN  7521                                           1250SALESMAN  7654                                           1250SALESMAN  7844                                           1500SALESMAN  小计                                           1400PRESIDENT 7839                                           5000PRESIDENT 小计                                           5000总计      小计                                     2073.21428--实例4：cube与grouping()结合使用select decode(grouping(job),1,'总计',job) sum,decode(grouping(empno),1,'小计',empno) empno,sum(sal)from empgroup by cube(job,empno);--SUM       EMPNO                                      SUM(SAL)--------- ---------------------------------------- ----------总计      小计                                          29025总计      7900                                            950总计      7369                                            800总计      7499                                           1600总计      7521                                           1250总计      7566                                           2975总计      7654                                           1250总计      7698                                           2850总计      7782                                           2450总计      7788                                           3000总计      7839                                           5000总计      7844                                           1500总计      7876                                           1100总计      7902                                           3000总计      7934                                           1300CLERK     小计                                           4150CLERK     7900                                            950CLERK     7369                                            800CLERK     7876                                           1100CLERK     7934                                           1300ANALYST   小计                                           6000ANALYST   7788                                           3000ANALYST   7902                                           3000MANAGER   小计                                           8275MANAGER   7566                                           2975MANAGER   7698                                           2850MANAGER   7782                                           2450SALESMAN  小计                                           5600SALESMAN  7499                                           1600SALESMAN  7521                                           1250SALESMAN  7654                                           1250SALESMAN  7844                                           1500PRESIDENT 小计                                           5000PRESIDENT 7839                                           5000--