Oracle GROUPING SETS

-- Start

大家对GROUP BY应该比较熟悉吧。还记得当初学习SQL的时候，总是理解不了GROUP BY的作用，经过好长时间才终于明白GROUP BY的真谛。当然，这和我本人笨也有关系，呵呵。下面我们先看一个例子。

CREATE TABLE employee
(
  name          NVARCHAR2(10),
  gender        NCHAR(1),
  country       NVARCHAR2(10),
  department    NVARCHAR2(10),
  salary        NUMBER(10)
);
INSERT INTO employee VALUES ('张三','男','中国','市场部',4000);
INSERT INTO employee VALUES ('李四','男','中国','市场部',5000);
INSERT INTO employee VALUES ('王五','女','美国','市场部',3000);  
INSERT INTO employee VALUES ('赵红','男','中国','技术部',2000);
INSERT INTO employee VALUES ('李白','女','中国','技术部',5000);  
INSERT INTO employee VALUES ('王蓝','男','美国','技术部',4000);

假设现在让你统计一下每个国家的平均工资和每个部门的平均工资，怎么办？我们可以使用 UNION ALL。

SELECT country, null department, round(avg(salary), 2) FROM employee GROUP BY country
UNION ALL
SELECT null country, department, round(avg(salary), 2) FROM employee GROUP BY department;

有没有更简单的方法呢？答案是肯定的。

SELECT country, department, round(avg(salary), 2) FROM employee GROUP BY GROUPING SETS (country, department);

怎么样？是不是很简单。从上面的例子我们可以得到下面的等价关系。

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

我们还可以使用括号。

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语句中多次使用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) 

--更多参见：Oracle SQL 精萃

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

-- Last edited on 2015-02-28

-- Created by ShangBo on 2015-02-28

-- End