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