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

假设有表test1,数据如下:

COL1COL2COL51a         101b         101c         101d         101e         101f         202a         202b         102c         103a         103b         304a         104b         104c         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;