Oracle-Grouping sets

-- 创建一个雇员表
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);
-- 查看数据
SELECT
	* 
FROM
	employee;
-- 我们可以直接选取一个常量，而不用从表中选取，例如下面的null
-- 使用round(number,pre)表示number四舍五入到pre位
-- 使用union将两个集合合并
SELECT
	a.country,
	NULL department,
	round( avg( a.salary ), 2 ) AS salary 
FROM
	employee a 
GROUP BY
	a.country UNION ALL
SELECT NULL
	country,
	a.department,
	round( avg( a.salary ), 2 ) AS salary 
FROM
	employee a 
GROUP BY
	a.department
-- 使用以下语句就可以做到以上语句的效果
/*
GROUP BY grouping sets ( country, department );
相当于
GROUP BY country 
UNION ALL
GROUP BY department
*/
SELECT
	country,
	department,
	round( avg( salary ), 2 ) AS salary 
FROM
	employee 
GROUP BY
	grouping sets ( country, department );
/*
   
GROUP BY GROUPING SETS (A,(B,C))  等价与  GROUP BY A  
                                          UNION ALL  
                                          GROUP BY B,C
框号里面加括号表示是一个分组
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形式再进行组合
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) 
这种需要我们先将其翻译为最简单的group by形式再进行组合（你可以理解为像乘法的组合一样a(b+c)=ab+ac）
*/