group by rollup和group by cube的用法和区别

假设有表test1,数据如下:

COL1	COL2	COL5
1	a	10
1	b	10
1	c	10
1	d	10
1	e	10
1	f	20
2	a	20
2	b	10
2	c	10
3	a	10
3	b	30
4	a	10
4	b	10
4	c	20

group by rollup只对第一列分组汇总,例如group by rollup(col1,col2),表示先对对col1,col2分组汇总,再对col1分组汇总,最后不分组汇总(不对col2分组汇总)

select col1,col2,sum(col5) from test1 group by rollup(col1,col2) order by col1,col2;

结果如下:

 

group by cube对所有列分组汇总,例如group by cube(col1,col2),表示先对col1,col2分组汇总,再对col1分组汇总,再对col2分组分组汇总,最后部分在汇总

select col1,col2,sum(col5) from test1 group by cube(col1,col2) order by col1,col2;