GROUPING SETS、ROLLUP、CUBE

----start

转自http://blog.csdn.net/shangboerds/article/details/5193211

    大家对GROUP BY应该比较熟悉，如果你感觉自己并不完全理解GROUP BY，那么本文不适合你。还记得当初学习SQL的时候，总是理解不了GROUP BY的作用，经过好长时间才终于明白GROUP BY的真谛。当然，这和我本人笨也有关系，但是GROUP BY的确不好理解。本文将介绍DB2 GROUPING SETS、ROLLUP、CUBE的使用方法，这些关键字比GROUP BY更难理解，所以阅读本文的时候，一定要慢，仔细的分析，你理解的越多，需要记忆的就越少。

    我们首先来看GROUPING SETS的使用方法，请看下面的例子

[c-sharp]  view plain copy print ?

1. GROUP BY GROUPING SETS (A,B,C)  等价与  GROUP BY A  
2.                                         UNION ALL  
3.                                         GROUP BY B  
4.                                         UNION ALL  
5.                                         GROUP BY C  

 

从字面上理解，GROUPING SETS就是GROUP集合的意思，确实是这样的，从上面的例子，我们可以很容易的理解GROUPING SETS的使用方法，但是使用括号的时候需要我们特别注意，请看下面的例子

[c-sharp]  view plain copy print ?

1. GROUP BY GROUPING SETS ((A,B,C))  等价与  GROUP BY A,B,C  
2.    
3.    
4. GROUP BY GROUPING SETS (A,(B,C))  等价与  GROUP BY A  
5.                                           UNION ALL  
6.                                           GROUP BY B,C  

 

    我们应该把括号里面的所有内容看做一个整体，这个整体必须在同一个GROUP BY语句中，例如，语句2中的B,C在括号中，B,C必须在同一个GROUP BY语句中，千万别把他们拆开，写出GROUP BY B UNION ALL GROUP BY C，那样就大错特错了。

    我们还可以在一个GROUP BY语句中多次使用GROUPING SETS，如下：

[c-sharp]  view plain copy print ?

1. GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B,C  
2.         ,GROUPING SETS (B)  
3.         ,GROUPING SETS (C)  
4.    
5.    
6. GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B,C  
7.         ,GROUPING SETS ((B,C))   
8.    
9.    
10. GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B  
11.         ,GROUPING SETS (B,C)        UNION ALL  
12.                                     GROUP BY A,C  

 

    我们还可以混合使用，如下：

[c-sharp]  view plain copy print ?

1. GROUP BY A                     等价于  GROUP BY A  
2.         ,B                                     ,B  
3.         ,GROUPING SETS ((B,C))                 ,C  
4.    
5.    
6. GROUP BY A                    等价于  GROUP BY A,B,C  
7.         ,B                            UNION ALL  
8.         ,GROUPING SETS (B,C)          GROUP BY A,B  
9.    
10.    
11. GROUP BY A                    等价于 GROUP BY A,B,C  
12.         ,B                           UNION ALL  
13.         ,C                           GROUP BY A,B,C  
14.         ,GROUPING SETS (B,C)   

 

请特别注意上面的第3条语句。

    下面我们介绍一下ROLLUP和CUBE关键字，它们的使用方式类似，作用也类似，都是用来为GROUP BY语句返回的结果添加汇总信息，也可以说，它们是对分组结果进行二次分组。下面我们看一个简单的例子，如下：

[c-sharp]  view plain copy print ?

1. SELECT    
2.     DEPT   AS 部门,  
3.     SEX    AS 性别,  
4.     AVG(SALARY) AS 平均工资  
5. FROM    
6. (    
7.     --姓名  性别  部门  工资    
8.     VALUES    
9.     ('张三','男','市场部',4000),  
10.     ('赵红','男','技术部',2000),  
11.     ('李四','男','市场部',5000),  
12.     ('李白','女','技术部',5000),  
13.     ('王五','女','市场部',3000),  
14.     ('王蓝','女','技术部',4000)  
15. ) AS EMPLOY(NAME,SEX,DEPT,SALARY)  
16. GROUP BY ROLLUP(DEPT,SEX)  
17. ORDER BY 部门,性别  
18.    
19.    
20. 查询结果：  
21. 部门          性别        平均工资  
22. 市场部         女          3000  
23. 市场部         男          4500  
24. 市场部         NULL        4000  
25. 技术部         女          4500  
26. 技术部         男          2000  
27. 技术部         NULL        3666  
28. NULL           NULL        3833  

 

值得注意的是，上面的ROLLUP语句中，部门(DEPT)和性别(SEX)的顺序非常重要，如果我们互换一下它两的顺序，将得到不同的结果，如下：

[c-sharp]  view plain copy print ?

1. SELECT    
2.     SEX    AS 性别,  
3.     DEPT   AS 部门,  
4.     AVG(SALARY) AS 平均工资  
5. FROM    
6. (    
7.     --姓名  性别  部门  工资    
8.     VALUES    
9.     ('张三','男','市场部',4000),  
10.     ('赵红','男','技术部',2000),  
11.     ('李四','男','市场部',5000),  
12.     ('李白','女','技术部',5000),  
13.     ('王五','女','市场部',3000),  
14.     ('王蓝','女','技术部',4000)  
15. ) AS EMPLOY(NAME,SEX,DEPT,SALARY)  
16. GROUP BY ROLLUP(SEX,DEPT)  
17. ORDER BY 性别,部门  
18.    
19.    
20. 查询结果：  
21. 性别         部门         平均工资  
22. 女         市场部         3000  
23. 女         技术部         4500  
24. 女         NULL           4000  
25. 男         市场部         4500  
26. 男         技术部         2000  
27. 男         NULL           3666  
28. NULL       NULL           3833  

 

CUBE语句比ROLLUP语句返回更多的内容，以下是将上面语句的ROLLUP替换为CUBE后得到的结果：

[c-sharp]  view plain copy print ?

1. SELECT    
2.    DEPT   AS 部门,  
3.     SEX    AS 性别,      
4.     AVG(SALARY) AS 平均工资  
5. FROM    
6. (    
7.     --姓名  性别  部门  工资    
8.     VALUES    
9.     ('张三','男','市场部',4000),  
10.     ('赵红','男','技术部',2000),  
11.     ('李四','男','市场部',5000),  
12.     ('李白','女','技术部',5000),  
13.     ('王五','女','市场部',3000),  
14.     ('王蓝','女','技术部',4000)  
15. ) AS EMPLOY(NAME,SEX,DEPT,SALARY)  
16. GROUP BY CUBE(DEPT,SEX)  
17. ORDER BY 部门,性别  
18.    
19.    
20. 查询结果：  
21. 部门         性别         平均工资  
22. 市场部         女         3000  
23. 市场部         男         4500  
24. 市场部         NULL       4000  
25. 技术部         女         4500  
26. 技术部         男         2000  
27. 技术部         NULL       3666  
28. NULL           女         4000  
29. NULL           男         3666  
30. NULL           NULL       3833  

 

如果我们替换CUBE语句中部门(DEPT)和性别(SEX)的顺序，我们将会得到相同的结果。

----更多参见：DB2 SQL 精萃