2005-05-10 09:40 First Publish.
Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果：


Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。




除本文内容外，你还可参考：
分析函数参考手册： http://xsb.itpub.net/post/419/33028
分析函数使用例子介绍：http://xsb.itpub.net/post/419/44634

SQL> create table t as select * from dba_indexes;


表已创建。


SQL> select index_type, status, count(*) from t group by index_type, status;


INDEX_TYPE STATUS COUNT(*)
--------------------------- -------- ----------
LOB VALID 51
NORMAL N/A 25
NORMAL VALID 479
CLUSTER VALID 11


下面来看看ROLLUP和CUBE语句的执行结果。


SQL> select index_type, status, count(*) from t group by rollup(index_type, status);


INDEX_TYPE STATUS COUNT(*)
--------------------------- -------- ----------
LOB VALID 51
LOB 51
NORMAL N/A 25
NORMAL VALID 479
NORMAL 504
CLUSTER VALID 11
CLUSTER 11
566


已选择8行。


SQL> select index_type, status, count(*) from t group by cube(index_type, status);


INDEX_TYPE STATUS COUNT(*)
--------------------------- -------- ----------
566
N/A 25
VALID 541
LOB 51
LOB VALID 51
NORMAL 504
NORMAL N/A 25
NORMAL VALID 479
CLUSTER 11
CLUSTER VALID 11


已选择10行。


查询结果不是很一目了然，下面通过Oracle提供的函数GROUPING来整理一下查询结果。


SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)
2 from t group by rollup(index_type, status) order by 1, 2;


G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB VALID 51
0 0 NORMAL N/A 25
0 0 NORMAL VALID 479
0 0 CLUSTER VALID 11
0 1 LOB 51
0 1 NORMAL 504
0 1 CLUSTER 11
1 1 566


已选择8行。


这个查询结果就直观多了，和不带ROLLUP语句的GROUP BY相比，ROLLUP增加了对INDEX_TYPE的GROUP BY统计和对所有记录的GROUP BY统计。


也就是说，如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。


下面看看CUBE语句。


SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*) 
2 from t group by cube(index_type, status) order by 1, 2;


G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB VALID 51
0 0 NORMAL N/A 25
0 0 NORMAL VALID 479
0 0 CLUSTER VALID 11
0 1 LOB 51
0 1 NORMAL 504
0 1 CLUSTER 11
1 0 N/A 25
1 0 VALID 541
1 1 566


已选择10行。


和ROLLUP相比，CUBE又增加了对STATUS列的GROUP BY统计。


如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。


除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUP BY结果。


SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*) 
2 from t group by rollup(index_type, status) order by 1;


G_IND INDEX_TYPE STATUS COUNT(*)
---------- --------------------------- -------- ----------
0 LOB VALID 51
0 NORMAL N/A 25
0 NORMAL VALID 479
0 CLUSTER VALID 11
1 LOB 51
1 NORMAL 504
1 CLUSTER 11
3 566


已选择8行。


SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*) 
2 from t group by cube(index_type, status) order by 1;


G_IND INDEX_TYPE STATUS COUNT(*)
---------- --------------------------- -------- ----------
0 LOB VALID 51
0 NORMAL N/A 25
0 NORMAL VALID 479
0 CLUSTER VALID 11
1 LOB 51
1 NORMAL 504
1 CLUSTER 11
2 N/A 25
2 VALID 541
3 566


已选择10行。

grouping_id()可以美化效果：

select DECODE(GROUPING_ID(C1), 1, '合计', C1) D1,
DECODE(GROUPING_ID(C1, C2), 1, '小计', C2) D2,
DECODE(GROUPING_ID(C1, C2, C1 + C2), 1, '小计', C1 + C2) D3,
count(*),
GROUPING_ID(C1, C2, C1 + C2, C1 + 1, C2 + 1),
GROUPING_ID(C1)
from T2
group by rollup(C1, C2, C1 + C2, C1 + 1, C2 + 1);

===========================================================

1.报表合计专用的Rollup函数

销售报表

广州 1月 2000元

广州 2月 2500元

广州 4500元

深圳 1月 1000元

深圳 2月 2000元

深圳 3000元

所有地区 7500元



以往的查询SQL:

Select area,month,sum(money) from SaleOrder group by area,month

然后广州，深圳的合计和所有地区合计都需要在程序里自行累计


1.其实可以使用如下SQL:

Select