ITPub博客

首页 > 应用开发 > IT综合 > ROLLUP和CUBE 用法

ROLLUP和CUBE 用法

原创 IT综合 作者:lvqf 时间:2011-03-31 11:35:51 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-10

  • 博文量
    13
  • 访问量
    26596