Oracle分组扩展函数的使用(主要增加小计及合计金额)

原创 2012年03月26日 12:07:14
  1.     在oracle及其它数据库中通常使用group by 进行数据分组,oracle在group by 的基础上提供了一组分组的扩展函数,这组扩展函数主要用于增加小计及总计记录,主要包括用于group by 之后的rollup,cube,grouping sets,和用于查询列或having子句中的grouping(单列)标识如果该列值为空则返回值为1,grouping_id(多列)将会计算返回的结果值(我们将会在下面进行详细的讨论),group_id()标识是否有重复数据,下面我们首先创建一个测试表:

      create table test_group_table(

            t_year number,

           t_month number,

           quantity number

     );

     然后插入相应的测试数据:

       insert into test_group_table values(2012,1,29);

       insert into test_group_table values(2012,1,39);

       insert into test_group_table values(2012,2,11);

       insert into test_group_table values(2012,3,12);

       insert into test_group_table values(2013,1,10);

       insert into test_group_table values(2013,2,20);

       insert into test_group_table values(2013,3,30);

       insert into test_group_table values(2013,4,40);

    正常状态下,我们使用普通的group by 子句: select t_year,t_month, sum(quantity) qty from test_group_table group by t_year,t_month;得到的返回结果如下:

    2012 3 12
    2012 1 68
    2013 2 20
    2012 2 11
    2013 1 10
    2013 3 30
    2013 4 40

    上面的结果不不包含合计及小计的内容;这时如果我们使用分组函数rollup,该函数将会在结果集中增加小计及合计的内容,执行select t_year,t_month, sum(quantity) qty from test_group_table group byrollup(t_year,t_month),得到的结果如下:

2012   1    30
2012   2    43.5
2012   3    55.5
2012   4    20
2012         149
2013   1    808
2013         808
                  957

上面结果集中红色部分即是/根据年份(t_year)的小计及最后的所有数量的合计值.

如果我们使用cube分组函数时,执行分组sql(select t_year,t_month, sum(quantity) qty from test_group_table group bycube(t_year,t_month);)得到的结果如下:

        957
 1    838
 2    43.5
 3    55.5
 4     20
2012  149
2012 1 30
2012 2 43.5
2012 3 55.5
2012 4 20
2013  808
2013 1 808
上面的结果中红色部分即为cube结果比rollup多出来的针对月份(t_month)的合计数量值。

而使用grouping sets扩展分组函数时,即执行sql(select t_year,t_month, sum(quantity) qty from test_group_table group bygroupint  sets(t_year,t_month)),将返回的结果集为为:

       1   838
       2   43.5
       3   55.5
       4   20
 2013  808
 2012  149

该结果集比cube的结果集只有针对年份和针对月份的分组值,并且没有合部记录的合计值;

下面我们再说一下having及select 后面可以使用的grouping(单列)函数,grouping_id(多列),及group_id()的使用方法:

  •     grouping(单列)

     select t_year,t_month, sum(quantity) qty,grouping(t_year),grouping(t_month) from test_group_table group byrollup(t_year,t_month);返回的结果集为:

           2012   1    30           0    0
           2012   2    43.5        0    0
           2012   3    55.5        0    0
           2012   4    20           0     0
           2012       149           0     1
           2013   1    808         0     0
           2013   5    907.54   0     0
           2013       1715.54   0     1
           
                1864.54   1      1

      通过上面的结果集我们可以看到最后一列是全部结果的合计值,因此年份和月份列均为空因此均返回了1,而第5行和第8行的小计行,因为年份不为空,但月份为空,因此grouping(t_year)返回了0,而grouping(t_month)返回了1,注意,如果t_year列本身值即为空,则grouping(t_year)仍然返回0;

      如果我们只想返回合部合计的结果集,则可以在having子句中增加相应的判断,执行sql(select t_year,t_month, sum(quantity) qty,grouping(t_year),grouping(t_month) from test_group_table group byrollup(t_year,t_month) having grouping(t_year) = 1 and grouping(t_month) = 1)将只返回:

                       1864.54   1      1

      而如果执行select t_year,t_month, sum(quantity) qty,grouping(t_year),grouping(t_month) from test_group_table group by rollup(t_year,t_month) havinggrouping(t_year) = 0 and grouping(t_month) = 1

    则返回 的结果集为:

            2012       149           0     1
           
2013       1715.54   0     1

    因此我们可以使用grouping(单列)来进行相关数据的过滤操作.

  • grouping_id(多列)

              grouping_id(多列)的使用方法基本与grouping(单列)类似,但grouping_id可以传入多列,如果转入一列的情况下,它的返回结果也是列值为空则返回1,否则返回0;但如果参数转入为多列,则其的计算方式如下,我们以grouping(t_year,t_month)为例说明:

             如果t_year,t_month列均为空则grouping_id(t_year,t_month)返回值为二进制的11,转为10进制为3;

                     t_year为空,t_month不为空则返回值为10,转为10进制为2;

                     t_year不为空,t_month为空,则返回值为01转为10进制为1;

  • group_id()不接收任何参数,如果返回结果集中有重复记录,则该值为重复次数,执行sql:
    SELECT T_YEAR,
           T_MONTH,
           sum(quantity),
           group_id()
      FROM TEST_REPORT_TABLE
      group by rollup(t_year,t_month),t_month;
    返回的结果集为: 
         2012 1 30 0
    2013 1 808 0
    2012 2 43.5 0
    2012 3 55.5 0
    2012 4 20 0
    2013 5 907.54 0
    2012 1 30 1
    2013 1 808 1
    2012 2 43.5 1
    2012 3 55.5 1
    2012 4 20 1
    2013 5 907.54 1
     1 838 0
     2 43.5 0
     3 55.5 0
     4 20 0
     5 907.54 0
    红色部分因为存在一次重复的记录,group_id()返回值为1

相关文章推荐

Oracle分组小计、总计示例(grouping sets的使用)

1.首先创建一个表 Sql代码 create table TE ( ID VARCHAR2(2), T_CODE VARCHAR2(4), T_...
  • lqh4188
  • lqh4188
  • 2012年05月24日 09:48
  • 10375

oracle分组汇总统计函数grouping

前两天同事问一个oracle使用grouping完成一个统计报表的功能,这个函数帅呆了。开发分组报表直接一个SQL就搞定。 grouping(columnA)函数的意思:当前行如果是由rollup汇总...

oracle group by rollup实现小计、合计

oracle group by rollup实现小计、合计

Oracle 使用分析函数实现小计合计

select grouping(vsaltype) as sq,                          vsaltype || '小计计' vsaltype,             ...

ORACLE中每个分组后加个合计,最后总合计的GROUP BY rollup,和中文排序

参考代码如下 SELECT LOCAL_NAME , DEPT_DISTINGUISH_NAME , SUM(TOTAL_PAY_SUM_R_LDC) TOTAL_PAY_SUM_R...

Oracle关于Group by合计、小计的使用

方法:group by rollup(col1,col2) group by cube(col1,col2) 具体例子: select G_GOODS_OUT.DEPTID, sum(decod...

EXTJS. Grid总计+oracle小计合计

直接在客户端统计,动态的修改Grid(js 操作)。  实现代码  function GridSum(grid){    var sum = 0;    grid.store.each(fun...

OLAP 支持---ROLLUP和CUBE语句 group by 的增强版本语句!可进行分层分组模式外 (小计与总计)

OLAP 支持 联机分析处理(On-Line Analytical Processing,简称 OLAP)具有在一条 SQL 语句中执行复杂数据分析的功能,从而通过减少对数据库的查...
  • mfkpie
  • mfkpie
  • 2015年07月12日 22:59
  • 1103

图学PowerBuilder----Datawindow数据列表分组小计功能

需求:要求列出一系列信息,和每种信息的发生次数。 分析:在SQL中实现时会出现这种冲突:如果列出数据详细信息,那么就不能分组,否则就不能获得所要数据的详细信息。 解决办法: 1、首先正常构建Da...
  • anyqu
  • anyqu
  • 2012年05月25日 11:01
  • 1856
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle分组扩展函数的使用(主要增加小计及合计金额)
举报原因:
原因补充:

(最多只允许输入30个字)