一、使用ROLLUP和CUBE汇总统计
1、测试数据
create table A
(
GRADE CHAR(5),
ID CHAR(5),
NUM CHAR(5)
)
insert into a values('a',1,1);
insert into a values('a',2,2);
insert into a values('b',2,4);
insert into a values('b',3,4);
2、查看几个分组结果
SQL> select sum(num) from a; //全部求和
SUM(NUM)
----------
11
SQL> select grade,sum(num) from a group by grade;//按grade分组
GRADE SUM(NUM)
----- ----------
a 3
b 8
SQL> select id,sum(num) from a group by id;;//按id分组
ID SUM(NUM)
----- ----------
1 1
2 6
3 4
SQL> select id,grade,sum(num) from a group by grade,id;;//按grade,id分组
ID GRADE SUM(NUM)
----- ----- ----------
1 a 1
2 a 2
2 b 4
3 b 4
3、使用rollup分组结果
SQL> select grade,id,sum(num) from a group by rollup(id,grade);
GRADE ID SUM(NUM)
----- ----- ----------
a 1 1
1 1
a 2 2
b 2 4
2 6
b 3 4
3 4
11
8 rows selected
SQL> select id,grade,sum(num) from a group by rollup(id,grade);
ID GRADE SUM(NUM)
----- ----- ----------
1 a 1
1 1
2 a 2
2 b 4
2 6
3 b 4
3 4
11
可以看出调换id和grade位置结果是不一样的,当使用rollup分组时,首先是按第一个参数分组,然后第一个,第二个参数分组 ,如果跟多的话依次类推
4、使用cube分组
SQL> select id,grade,sum(num) from a group by cube(id,grade);
ID GRADE SUM(NUM)
----- ----- ----------
11
a 3
b 8
1 1
1 a 1
2 6
2 a 2
2 b 4
3 4
3 b 4
10 rows selected
SQL> select grade,id,sum(num) from a group by cube(grade,id);
GRADE ID SUM(NUM)
----- ----- ----------
11
1 1
2 6
3 4
a 3
a 1 1
a 2 2
b 8
b 2 4
b 3 4
使用cube分组时调换参数的位置并不会影响结果,因为cube会为每个参数单独分组统计,然后按照然后第一个,第二个参数分组 ,第二个参,第三个参数一次分组统计
5、多个参数实验
(1)修改表
alter table a add grade1 char(5);
SQL> select * from a;
GRADE ID NUM GRADE1
----- ----- ----- ------
a 1 1 a1
a 2 2 a1
b 2 4 b1
b 3 4 b2
(2)使用rollup
SQL> select grade,grade1,id,sum(num) from a group by rollup(grade,grade1,id);
GRADE GRADE ID SUM(NUM)
----- ----- ----- ----------
a a1 1 1
a a1 2 2 按grade,grade1和id分组
a a1 3 按grade和grade1分组
a 3 按grade分组
b b1 2 4
b b1 4
b b2 3 4
b b2 4
b 8
11
(2)使用cube
SQL> select grade,grade1,id,sum(num) from a group by cube(grade,grade1,id);
GRADE GRADE ID SUM(NUM)
----- ----- ----- ----------
11
1 1
2 6 按id分组
3 4
a1 3 按grade1分组
a1 1 1 按grade1和id分组
a1 2 2
b1 4
b1 2 4
b2 4
b2 3 4
a 3 按grade分组
a 1 1
a 2 2
a a1 3 按grade和grade1分组
a a1 1 1 按grade,id和grade1分组
a a1 2 2
b 8
b 2 4 按grade,id分组
b 3 4
b b1 4
b b1 2 4
b b2 4
b b2 3 4
24 rows selected.
其实cube和rollup的区别就是cube采用排列组和,所有的参数排列组合,儿而rollup只是依次组合