-- 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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)