Oracle ROLLUP和CUBE 用法

rollup只有第一个参数起作用也是理解不正确的，先看看例子吧：

 

 SQL>   select   grade,id,num   from   a;  
   
  GRADE             ID                                 NUM  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  b                     3                                       4  
  b                     4                                       4  
   
  对grade字段进行rollup：  
   
  SQL>   select   grade,sum(num)   from   a   group   by   rollup(grade);  
   
  GRADE                 SUM(NUM)  
  ----------   ----------  
  a                                       3  
  b                                       8  
                                        11  
  同时对grade和id字段进行rollup  
  SQL>   SELECT   decode(grouping_id(grade,ID),2,'小计',3,'合计',grade)   grade,  
      2                   decode(grouping_id(grade,ID),1,'小计',3,'合计',ID)   ID,  
      3                   SUM(num)  
      4     FROM   a   GROUP   BY   ROLLUP(grade,ID)  
      5     /  
   
  GRADE             ID                       SUM(NUM)  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  a                     小计                                 3  
  b                     3                                       4  
  b                     4                                       4  
  b                     小计                                 8  
  合计               合计                               11  
   
  7   rows   selected  
   
  再看看先对grade分组，再对id进行rollup的情况：  
   
  SQL>   SELECT   grade,  
      2                   decode(GROUPING(ID),1,'合计',ID)   ID,  
      3                   SUM(num)  
      4     FROM   a   GROUP   BY   grade,rollup(ID)  
      5     /  
   
  GRADE             ID                       SUM(NUM)  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  a                     合计                                 3  
  b                     3                                       4  
  b                     4                                       4  
  b                     合计                                 8  
   
  6   rows   selected  
   
  这里GROUP   BY   grade,rollup(ID)跟你的理解应该很相近了，而且可以看出GROUP   BY   grade,rollup(ID)结果跟ROLLUP(grade,ID)很类似，只是少了最后1行总合计，但是也可以就看出rollup多个字段时并不是只有1个字段起作用的  
   
  可以认为你理解的是只对第一个字段的累计，跟GROUP   BY   grade,rollup(ID)的结果很接近，再看rollup3个字段的情况：  
  SQL>   select   part,grade,id,num   from   a;  
   
  PART   GRADE             ID                                 NUM  
  ----   ----------   ----------   ----------  
  p1       a                     1                                       1  
  p1       a                     2                                       2  
  p1       b                     3                                       3  
  p1       b                     4                                       4  
  p2       c                     5                                       5  
  p2       d                     6                                       6  
   
  6   rows   selected  
   
  SQL>    
  SQL>   SELECT   decode(grouping_id(part,grade,ID),7,'总计',part)   part,  
      2                   decode(grouping_id(part,grade,ID),3,'小计',7,'总计',grade)   grade,  
      3                   decode(grouping_id(part,grade,ID),1,'小计',3,'小计',7,'总计',ID)   ID,  
      4                   SUM(num)  
      5     FROM   a   GROUP   BY   ROLLUP(part,grade,ID)  
      6     /  
   
  PART   GRADE             ID                       SUM(NUM)  
  ----   ----------   ----------   ----------  
  p1       a                     1                                       1  
  p1       a                     2                                       2  
  p1       a                     小计                                 3  
  p1       b                     3                                       3  
  p1       b                     4                                       4  
  p1       b                     小计                                 7  
  p1       小计               小计                               10  
  p2       c                     5                                       5  
  p2       c                     小计                                 5  
  p2       d                     6                                       6  
  p2       d                     小计                                 6  
  p2       小计               小计                               11  
  总计   总计               总计                               21  
   
  13   rows   selected  
   
  这里不光只对第一个字段做了累计，先按(part,grade,ID)分组累计,然后按(part,grade)分组累计，再按(part)分组累计，最后累计全部  
  再看看rollup   和   cube的区别：  
  对于ROLLUP(part,grade,ID),grouping_id(part,grade,ID)的值范围在（0，1，3，7）间即  
  part,grade,ID（作为合计时计为1）  
  0，0，0  
  0，0，1  
  0，1，1  
  1，1，1  
  而对于cube(part,grade,ID),grouping_id(part,grade,ID)的值范围在0-7之间即  
  part,grade,ID（作为合计时计为1）  
  0，0，0  
  0，0，1  
  0，1，0  
  0，1，1  
  1，0，0  
  1，0，1  
  1，1，0  

评论

1 楼 dolphin_ygj 2009-06-28  

CREATE   TEST   TABLE   AND   INSERT   TEST   DATA.  
                                  create   table   students  
                                  (id   number(15,0),  
                                  area   varchar2(10),  
                                  stu_type   varchar2(2),  
                                  score   number(20,2));  
                                   
                                  insert   into   students   values(1,   '111',   'g',   80   );  
                                  insert   into   students   values(1,   '111',   'j',   80   );  
                                  insert   into   students   values(1,   '222',   'g',   89   );  
                                  insert   into   students   values(1,   '222',   'g',   68   );  
                                  insert   into   students   values(2,   '111',   'g',   80   );  
                                  insert   into   students   values(2,   '111',   'j',   70   );  
                                  insert   into   students   values(2,   '222',   'g',   60   );  
                                  insert   into   students   values(2,   '222',   'j',   65   );  
                                  insert   into   students   values(3,   '111',   'g',   75   );  
                                  insert   into   students   values(3,   '111',   'j',   58   );  
                                  insert   into   students   values(3,   '222',   'g',   58   );  
                                  insert   into   students   values(3,   '222',   'j',   90   );  
                                  insert   into   students   values(4,   '111',   'g',   89   );  
                                  insert   into   students   values(4,   '111',   'j',   90   );  
                                  insert   into   students   values(4,   '222',   'g',   90   );  
                                  insert   into   students   values(4,   '222',   'j',   89   );  
                                  commit;  
                   
                                  col   score   format   999999999999.99  
  ROLLUP  
                   
                                  select   id,area,stu_type,sum(score)   score    
                                  from   students  
                                  group   by   rollup(id,area,stu_type)  
                                  order   by   id,area,stu_type;  
                                   
                                  /*--------理解rollup  
                                  select   a,   b,   c,   sum(   d   )  
                                  from   t  
                                  group   by   rollup(a,   b,   c);  
                                   
                                  等效于  
                                   
                                  select   *   from   (  
                                    select   a,   b,   c,   sum(   d   )   from   t   group   by   a,   b,   c    
                                    union   all  
                                    select   a,   b,   null,   sum(   d   )   from   t   group   by   a,   b  
                                    union   all  
                                    select   a,   null,   null,   sum(   d   )   from   t   group   by   a  
                                    union   all  
                                    select   null,   null,   null,   sum(   d   )   from   t  
                                  )  
                                  */  
                   
  CUBE  
                   
                                  select   id,area,stu_type,sum(score)   score    
                                  from   students  
                                  group   by   cube(id,area,stu_type)  
                                  order   by   id,area,stu_type;  
                                   
                                  /*--------理解cube  
                                  select   a,   b,   c,   sum(   d   )   from   t  
                                  group   by   cube(   a,   b,   c)  
                                   
                                  等效于  
                                   
                                  select   a,   b,   c,   sum(   d   )   from   t  
                                  group   by   grouping   sets(    
                                            (   a,   b,   c   ),    
                                            (   a,   b   ),   (   a   ),   (   b,   c   ),    
                                            (   b   ),   (   a,   c   ),   (   c   ),    
                                            ()   )  
                                  )  
                                  */  
   
    GROUPING  
                   
                                  从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,  
                                  如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!  
                                   
                                  select   decode(grouping(id),1,'all   id',id)   id,  
                                                  decode(grouping(area),1,'all   area',to_char(area))   area,  
                                                  decode(grouping(stu_type),1,'all_stu_type',stu_type)   stu_type,  
                                                  sum(score)   score  
                                  from   students  
                                  group   by   cube(id,area,stu_type)  
                                  order   by   id,area,stu_type;