关于rollup,cube,grouping sets的个人理解

1.关于group by rollup的理解

group by(字段1，字段2，字段3，字段4)

oracle先group by(字段1，字段2，字段3，字段4),group by(字段1，字段2，字段3),group by(字段1，字段2),

group by(字段1),最后对查询结果总的group by

 

例：

select cgicode,stockyear,stockmonth,vdc, sum(fcy*vdc) from priceadjust
 where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
  group by rollup(cgicode,stockyear,stockmonth,vdc) order by cgicode

 

1   JLY0000002   2007    9   1     0
2   JLY0000002   2007    9   0
3   JLY0000002   2007                0
4   JLY0000002   2008    8   1     0
5   JLY0000002   2008    8   0
6   JLY0000002   2008    9   1     -152
7   JLY0000002   2008    9          -152
8   JLY0000002   2008                -152
9   JLY0000002   2009    9   -1    -3121.9
10 JLY0000002   2009    9    1    8351.6
11 JLY0000002   2009    9          5229.7
12 JLY0000002   2009                5229.7
13 JLY0000002                           5077.7
14 JLY0000006   2009     9   -1   -300
15 JLY0000006   2009     9    1   402.6
16 JLY0000006   2009     9        102.6
17 JLY0000006   2009               102.6
18 JLY0000006                          102.6

19                                              5180.3

                                  

 

2.关于group by rollup(字段1),字段2,字段3的理解

首先以字段2，字段3分组，合计相同的字段1

后面再合计字段2，字段3

 

例：

select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
 where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
  group by rollup(cgicode),stockyear,stockmonth order by cgicode

 

1 JLY0000002 2007 9 0
2 JLY0000002 2008 9 -152
3 JLY0000002 2009 9 5229.7
4 JLY0000002 2008 8 0
5 JLY0000006 2009 9 102.6
6                     2009 9 5332.3
7                     2008 9 -152
8                     2008 8 0
9                     2007 9 0

 

3.关于group by cube(字段1，字段2，字段3)的理解

首先group by(字段1，字段2，字段3),group by(字段1，字段2),

group by(字段1，字段3),group by(字段1),group by(字段2，字段3),

group by(字段2),group by(字段3),最后对查询结果在group by.

 

例：

select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
 where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
  group by cube(cgicode,stockyear,stockmonth) order by cgicode

 

1     JLY0000002       2007       9          0
2     JLY0000002       2007                   0
3     JLY0000002       2008       8          0
4     JLY0000002       2008       9          -152
5     JLY0000002       2008                   -152
6     JLY0000002       2009       9          5229.7
7     JLY0000002       2009                   5229.7
8     JLY0000002                      8          0
9     JLY0000002                      9          5077.7
10   JLY0000002                                  5077.7
11   JLY0000006        2009      9          102.6
12   JLY0000006        2009                  102.6
13   JLY0000006                      9          102.6
14   JLY0000006                                  102.6
15                              2007      9          0
16                              2007                  0
17                              2008       8         0
18                              2008       9         -152
19                              2008                  -152
20                              2009       9         5332.3
21                              2009                  5332.3
22                                              8         0
23                                              9         5180.3
24                                                         5180.3 

 

4.关于group by cube(字段1),字段2,字段3的理解

同2，只是排序有不同

 

例：

select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
 where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
  group by cube(cgicode),stockyear,stockmonth order by cgicode

1     JLY0000002        2009        9        5229.7
2     JLY0000002        2007        9        0
3     JLY0000002        2008        9        -152
4     JLY0000002        2008        8         0
5     JLY0000006        2009        9        102.6
6                                2009        9        5332.3
7                                2008        8        0
8                                2007        9        0
9                               2008         9        -152

 

 

 

 5.关于group by grouping sets(字段1，字段2，字段3)的理解

group by(字段1),group by(字段2),group by(字段3)

 

例如：

select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
 where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
  group by grouping sets(cgicode,stockyear,stockmonth) order by cgicode

 

1      JLY0000002                             5077.7
2      JLY0000006                             102.6
3                             2009                 5332.3
4                             2007                  0
5                             2008                 -152
6                                              8       0
7                                              9       5180.3

 

6.关于group by grouping sets(字段1),字段2，字段3的理解

group by 字段1，字段2，字段3

 

例如：

1          JLY0000002     2007     9        0
2          JLY0000002     2008     8        0
3          JLY0000002     2008     9        -152
4          JLY0000002     2009     9        5229.7
5          JLY0000006     2009     9        102.6

 

 

7.关于group by grouping sets((字段1，字段2，字段3))的理解

注意：比5多加了一个括号

结果同6

 

例：

 

select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
 where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
  group by grouping sets((cgicode,stockyear,stockmonth)) order by cgicode

 

1     JLY0000002     2007    9     0
2     JLY0000002     2008    8     0
3     JLY0000002     2008    9     -152
4     JLY0000002     2009    9     5229.7
5     JLY0000006     2009    9     102.6

 

8.关于group by grouping sets(字段1，字段2),字段3的理解

   grouping by (字段1，字段3) 然后 grouping by(字段2，字段3)

 

例：

 

select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
 where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
  group by grouping sets(cgicode,stockyear),stockmonth order by cgicode

 

1      JLY0000002                    8      0
2      JLY0000002                    9      5077.7
3      JLY0000006                    9      102.6
4                              2007       9       0
5                              2008       8       0
6                              2008       9       -152
7                              2009       9        5332.3

 

 

9.关于group by grouping sets((字段1，字段2，字段3),())的理解

   group by((字段1，字段2，字段3),然后在对查询结果进行group by

 

例：

select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
 where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
  group by grouping sets((cgicode,stockyear,stockmonth),()) order by cgicode

 

1         JLY0000002        2007      9       0
2         JLY0000002        2008      8       0
3         JLY0000002        2008      9       -152
4         JLY0000002        2009      9       5229.7
5         JLY0000006        2009      9       102.6
6                                                           5180.3

 

结尾语：

    本文章是自己的个人理解，有什么不正之处，请读者不尽指正。

声明：

   本文章可任意转载，但需注明出处。