ROLLUP\CUBE简单例子

简介：


一、资源准备:
1:建表

create table TEST_1 
( 
  c1 VARCHAR2(10), 
  c2 VARCHAR2(10), 
  c3 VARCHAR2(10) 
);

2:数据准备

insert into TEST_1 (C1, C2, C3)
values ('1', 'A', '11');

insert into TEST_1 (C1, C2, C3)
values ('2', 'B', '22');

insert into TEST_1 (C1, C2, C3)
values ('3', 'B', '33');

insert into TEST_1 (C1, C2, C3)
values ('4', 'D', '44');

insert into TEST_1 (C1, C2, C3)
values ('5', 'E', '55');

insert into TEST_1 (C1, C2, C3)
values ('6', null, '66');

二、执行CUBE

select decode(grouping(c2), 1, 'Total', c2) c2, sum(c3), count(1)
  from test_1 t
 where t.c2 is not null
 group by cube(c2)

三、执行ROLLUP

select decode(grouping(c2), 1, 'Total', c2) c2, sum(c3), count(1)
  from test_1 t
 where t.c2 is not null
 group by rollup(c2)