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)
*/