假设有表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;