GROUPING SETS、ROLLUP、CUBE

 

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

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

GROUP BY GROUPING SETS (A,B,C)  等价与  GROUP BY A
                                        UNION ALL
                                        GROUP BY B
                                        UNION ALL
                                        GROUP BY C

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

GROUP BY GROUPING SETS ((A,B,C))  等价与  GROUP BY A,B,C


GROUP BY GROUPING SETS (A,(B,C))  等价与  GROUP BY A
                                          UNION ALL
                                          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，如下：

GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B,C
        ,GROUPING SETS (B)
        ,GROUPING SETS (C)


GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B,C
        ,GROUPING SETS ((B,C))


GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B
        ,GROUPING SETS (B,C)        UNION ALL
                                    GROUP BY A,C

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

GROUP BY A                     等价于  GROUP BY A
        ,B                                     ,B
        ,GROUPING SETS ((B,C))                 ,C


GROUP BY A                    等价于  GROUP BY A,B,C
        ,B                            UNION ALL
        ,GROUPING SETS (B,C)          GROUP BY A,B


GROUP BY A                    等价于 GROUP BY A,B,C
        ,B                           UNION ALL
        ,C                           GROUP BY A,B,C
        ,GROUPING SETS (B,C)

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

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

SELECT
    DEPT   AS 部门,
    SEX    AS 性别,
    AVG(SALARY) AS 平均工资
FROM
(
    --姓名  性别  部门  工资
    VALUES
    ('张三','男','市场部',4000),
    ('赵红','男','技术部',2000),
    ('李四','男','市场部',5000),
    ('李白','女','技术部',5000),
    ('王五','女','市场部',3000),
    ('王蓝','女','技术部',4000)
) AS EMPLOY(NAME,SEX,DEPT,SALARY)
GROUP BY ROLLUP(DEPT,SEX)
ORDER BY 部门,性别


查询结果：
部门          性别        平均工资
市场部         女          3000
市场部         男          4500
市场部         NULL        4000
技术部         女          4500
技术部         男          2000
技术部         NULL        3666
NULL           NULL        3833

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

SELECT
    SEX    AS 性别,
    DEPT   AS 部门,
    AVG(SALARY) AS 平均工资
FROM
(
    --姓名  性别  部门  工资
    VALUES
    ('张三','男','市场部',4000),
    ('赵红','男','技术部',2000),
    ('李四','男','市场部',5000),
    ('李白','女','技术部',5000),
    ('王五','女','市场部',3000),
    ('王蓝','女','技术部',4000)
) AS EMPLOY(NAME,SEX,DEPT,SALARY)
GROUP BY ROLLUP(SEX,DEPT)
ORDER BY 性别,部门


查询结果：
性别         部门         平均工资
女         市场部         3000
女         技术部         4500
女         NULL           4000
男         市场部         4500
男         技术部         2000
男         NULL           3666
NULL       NULL           3833

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

SELECT
   DEPT   AS 部门,
    SEX    AS 性别,
    AVG(SALARY) AS 平均工资
FROM
(
    --姓名  性别  部门  工资
    VALUES
    ('张三','男','市场部',4000),
    ('赵红','男','技术部',2000),
    ('李四','男','市场部',5000),
    ('李白','女','技术部',5000),
    ('王五','女','市场部',3000),
    ('王蓝','女','技术部',4000)
) AS EMPLOY(NAME,SEX,DEPT,SALARY)
GROUP BY CUBE(DEPT,SEX)
ORDER BY 部门,性别


查询结果：
部门         性别         平均工资
市场部         女         3000
市场部         男         4500
市场部         NULL       4000
技术部         女         4500
技术部         男         2000
技术部         NULL       3666
NULL           女         4000
NULL           男         3666
NULL           NULL       3833

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

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

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

----last updated on 2010.1.15

----written by ShangBo on 2010.1.15

----end