注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

有你做翅膀,孤单都是坚强

生命中总要经历曲折,或许是我们不愿意,突然到最后走不通,想重新来过,记录每件小事

 
 
 

日志

 
 
关于我
K24

曾经的生涩,曾经的懵懂,曾经的自恋,都随风去吧。。。

网易考拉推荐
 
 

GROUPING SETS、ROLLUP、CUBE  

2015-10-20 10:13:29|  分类: @sql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

---start

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

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

  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的使用方法,但是使用括号的时候需要我们特别注意,请看下面的例子

  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,如下:

  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  
 

    我们还可以混合使用,如下:

  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语句返回的结果添加汇总信息,也可以说,它们是对分组结果进行二次分组。下面我们看一个简单的例子,如下:

  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)的顺序非常重要,如果我们互换一下它两的顺序,将得到不同的结果,如下:

  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后得到的结果:

  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 精萃

----声明:转载请注明出处。

----last updated on 2010.1.15

----written by ShangBo on 2010.1.15

----end

  评论这张
 
阅读(23)| 评论(0)
推荐

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017