ROLLUP和CUBE 用法

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)可以美化效果

SQL> create table t3 as select * from dba_indexes;

Table created.

SQL> select count(1) from t3;

[@more@]

COUNT(1)
----------
1561

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*) from t3 group by rollup(index_type, status);

G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB N/A 1
0 0 LOB VALID 478
0 1 LOB 479
0 0 NORMAL N/A 56
0 0 NORMAL VALID 937
0 1 NORMAL 993
0 0 CLUSTER VALID 10
0 1 CLUSTER 10
0 0 IOT - TOP VALID 63
0 1 IOT - TOP 63
0 0 FUNCTION-BASED DOMAIN VALID 1

G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 1 FUNCTION-BASED DOMAIN 1
0 0 FUNCTION-BASED NORMAL VALID 15
0 1 FUNCTION-BASED NORMAL 15
1 1 1561

15 rows selected.

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*) from t3 group by cube(index_type, status);

G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
1 1 1561
1 0 N/A 57
1 0 VALID 1504
0 1 LOB 479
0 0 LOB N/A 1
0 0 LOB VALID 478
0 1 NORMAL 993
0 0 NORMAL N/A 56
0 0 NORMAL VALID 937
0 1 CLUSTER 10
0 0 CLUSTER VALID 10

G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 1 IOT - TOP 63
0 0 IOT - TOP VALID 63
0 1 FUNCTION-BASED DOMAIN 1
0 0 FUNCTION-BASED DOMAIN VALID 1
0 1 FUNCTION-BASED NORMAL 15
0 0 FUNCTION-BASED NORMAL VALID 15

17 rows selected.

以上说明，比rollup，cube增加了对status字段的group by统计

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22466621/viewspace-1048020/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22466621/viewspace-1048020/