# 小议rollup、cube、grouping sets、grouping、grouping_id在报表查询中的应用！

1、如果使用诸如group by rollup(A,B,C)的方式分组，那么返回的分组结果是
(A,B,C) (A,B) (A) (NULL) 一共四种结果。即从右到左递减，最后来个合计。

SQL> select * from t;

YEARS     MONTHS PRODUCT_NA      SALES
---------- ---------- ---------- ----------
2008          1 A                1000
2008          1 B                1500
2008          2 A                2000
2008          2 B                3000
2008          2 C                1000
2008          3 A                3000

SQL> select years,months,product_name,sum(sales) sum_sales from t
2  group by rollup(years,months,product_name)
3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
2008          1 A                1000 ----------group by (years,months,product_name)
2008          1 B                1500
2008          1                  2500 ----------group by (years,months)
2008          2 A                2000
2008          2 B                3000
2008          2 C                1000
2008          2                  6000 ----------group by (years,months)
2008          3 A                3000
2008          3                  3000 ----------group by (years,months)
2008                            11500 ----------group by (years)
11500 ----------group by (NULL)

2、如果使用诸如group by A,ROLLUP(B,C) 那么返回的分组方式是：
(A,B,C)  (A,B) (A,NULL)  及在这种情况下，先计算rollup里面的分组情况，再与A组合。

SQL> select years,months,product_name,sum(sales) sum_sales from t
2  group by years,rollup(months,product_name)
3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
2008          1 A                1000 ----------group by (years,months,product_name)
2008          1 B                1500
2008          1                  2500 ----------group by (years,months)
2008          2 A                2000
2008          2 B                3000
2008          2 C                1000
2008          2                  6000
2008          3 A                3000
2008          3                  3000
2008                            11500 ----------group by (years)

1、如果使用诸如cube(A,B,C)的方式，那么返回的分组组合是
(A) (A,B) (A,C) (A,B,C) (B) (B,C) (C) (null) 共8种组合方式

SQL> select years,months,product_name,sum(sales) sum_sales from t
2  group by cube(years,months,product_name)
3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
11500 ----------group by (null)
A                6000 ----------group by (product_name)
B                4500
C                1000
1                  2500
1 A                1000
1 B                1500
2                  6000
2 A                2000
2 B                3000
2 C                1000
3                  3000 ----------group by (months)
3 A                3000 ----------group by (months,product_name)
2008                            11500 ----------group by (years)
2008            A                6000
2008            B                4500
2008            C                1000 ----------group by (years,product_name)
2008          1                  2500
2008          1 A                1000
2008          1 B                1500
2008          2                  6000
2008          2 A                2000
2008          2 B                3000
2008          2 C                1000
2008          3                  3000 ----------group by (years,months)
2008          3 A                3000 ----------group by (years,months,product_name)

2、如果使用GROUP BY A,CUBE(B,C)，那么返回的分组组合为：
(A,B) (A,B,C) (A,C) (A)

SQL> select years,months,product_name,sum(sales) sum_sales from t
2  group by years,cube(months,product_name)
3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
2008                            11500 ----------group by (years)
2008            A                6000 ----------group by (years,product_name)
2008            B                4500
2008            C                1000
2008          1                  2500 ----------group by (years,months)
2008          1 A                1000 ----------group by (years,months,product_name)
2008          1 B                1500
2008          2                  6000
2008          2 A                2000
2008          2 B                3000
2008          2 C                1000
2008          3                  3000
2008          3 A                3000

3、如果使用GROUP BY A,ROLLUP(B,C),CUBE(D,E)，那么返回的分组组合为：

a,rollup(b,c),d,e
a,rollup(b,c),d
a,rollup(b,c),e
a,rollup(b,c)

a,b,c,d,e
a,b,d,e
a,d,e
a,b,c,d
a,b,d
a,d
a,b,c,e
a,b,e
a,e
a,b,c
a,b
a

SQL> select years,months,product_name,sum(sales) sum_sales from t
2  group by years,rollup(months),cube(product_name)
3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
2008          1 A                1000 ----------group by (years,months,product_name)
2008          2 A                2000
2008          3 A                3000
2008          1 B                1500
2008          2 B                3000
2008          2 C                1000
2008            A                6000 ----------group by (years,product_name)
2008            B                4500
2008            C                1000
2008          1                  2500 ----------group by (years,product_name)
2008          2                  6000
2008          3                  3000
2008                            11500 ----------group by (years)

SQL> select years,months,product_name,sum(sales) sum_sales from t
2  group by years,grouping sets(months,product_name)
3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
2008          2                  6000 ----------group by (years,months)
2008          1                  2500
2008          3                  3000
2008            B                4500 ----------group by (years,product_name)
2008            C                1000
2008            A                6000

-----------------------------------------------------------------------------------------------------------------华丽的分割线！

SQL> select months,product_name,sum(sales) sum_sales,grouping(product_name) from t
2  group by rollup(months,product_name)
3  /

MONTHS PRODUCT_NA  SUM_SALES GROUPING(PRODUCT_NAME)
---------- ---------- ---------- ----------------------
1 A                1000                      0 ----------group by (months,product_name)
1 B                1500                      0
1                  2500                      1 ----------group by (months)
2 A                2000                      0
2 B                3000                      0
2 C                1000                      0
2                  6000                      1 ----------group by (months)
3 A                3000                      0
3                  3000                      1 ----------group by (months)
11500                      1 ----------group by (null)

GROUPING_ID(A,B,C) = [101]二进制 = 5，
GROUPING_ID(B,A,C) = [011]二进制 = 3.

SQL> select years,months,product_name,sum(sales) sum_sales,grouping_id(years,months,product_name) g_id from t
2  group by rollup(years,months,product_name)
3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES       G_ID
---------- ---------- ---------- ---------- ----------
2008          1 A                1000          0
2008          1 B                1500          0
2008          1                  2500          1 ----------group by (years,months) 001=1
2008          2 A                2000          0
2008          2 B                3000          0
2008          2 C                1000          0
2008          2                  6000          1
2008          3 A                3000          0
2008          3                  3000          1
2008                            11500          3 ----------group by (years)   011=3
11500          7 ----------group by (null)    111=7

SQL> select decode(grouping(months)+grouping(product_name),1,'月份小计',2,'合计：',months) months,
2  product_name,sum(sales) sum_sales from t
3  group by rollup(months,product_name)
4  /

MONTHS                                   PRODUCT_NA  SUM_SALES
---------------------------------------- ---------- ----------
1                                        A                1000
1                                        B                1500

2                                        A                2000
2                                        B                3000
2                                        C                1000

3                                        A                3000

SQL> select decode(grouping_id(months,product_name),1,'月份小计：',2,'产品小计：',3,'合计：',months) months,
2  product_name,sum(sales) sum_sales from t
3  group by cube(months,product_name)
4  order by 2
5  /

MONTHS                                   PRODUCT_NA  SUM_SALES
---------------------------------------- ---------- ----------
1                                        A                1000
2                                        A                2000
3                                        A                3000

1                                        B                1500
2                                        B                3000

2                                        C                1000

• 博文量
35
• 访问量
92237