SELECT: GROUP BY¶
说明¶
将查询结果加以分组
参数¶
expr
列名或表达式。
rollup
rollup是group by 子句的简单扩展，rollup使select语句能够根据维度在分组的结果集中进行聚集操作（这取决于select的聚集函数）为每个分组返回一个小计，同时为所有分组返回总计。rollup会创建n+1级别的小计，其中n代表分组列的数量。
cube
rollup相当于cube的子集，部分cube类似于rollup，cube接受一组指定的分组列，并且为他们所有可能的组合创建小计。如果指定了CUBE，则结果集将包括等效rollup语句中包含的所有值以及其他组合。
rollup和cube的区别
rollup(a,b) 统计列包含：(a,b)、(a)、()
cube(a,b) 统计列包含：(a,b)、(a)、(b)、()
rollup(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a)、()
cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
grouping sets
grouping sets子句中所有的组将结果通过UNION ALL组合成一个结果集，GROUPING SETS的效果:1.只需要访问一次基表。2.不需要写很复杂的UNION语句。
注解
GROUP BY子句对输入按指定的表达式序列进行分组。对于形如GROUP BY expr1, expr2, ..., exprn的分组子句，对于其输出中任意组中的每个元组，expr1, expr2, ..., exprn的值都是相同的，但对于不同组中的元组，expr1, expr2, ..., exprn的值不完全相同。
GROUP BY子句中的表达式可以是简单的字段名，如:
SELECT SDEPT, COUNT(*) FROM STUDENT GROUP BY SDEPT;
将所有学生按其所在系进行分组；还可以是复杂表达式，如：
SELECT COUNT(*) FROM STUDENT GROUP BY SCORE >= 60;
将所有选课记录按及格与否进行分组，一组为及格的(SCORE >= 60为TRUE)，另一组为不及格的(SCORE >= 60为FALSE)。
若使用了GROUP BY子句，则在SELECT子句中选择列表中就不能直接引用那些不在GROUP BY子句中的列，如：
SELECT SNO, SCORE FROM ELECT GROUP BY SNO;
是错的，因为SCORE没有出现在GROUP BY子句中。
但通过使用聚集函数，则可以使用那些不在GROUP BY子句中的列，如：
SELECT SNO, AVG(SCORE) FROM ELECT GROUP BY SNO;
是正确的。
在GROUP BY子句中出现的表达式个数不可以超过128。
假设有一个表test，有A、B、C列，如果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。
GROUP BY ROLLUP(a, b, c)
等效于：
GROUP BY a, b, c UNION ALL
GROUP BY a, b UNION ALL
GROUP BY a UNION ALL
GROUP BY ().
假设有一个表test，有A、B、C列，如果使用group by cube(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后对(A、C)进行GROUP BY，
然后对(B、C)进行GROUP BY，然后是(A)进行GROUP BY，然后是(B)进行GROUP BY，然后是(C)进行GROUP BY，最后对全表进行GROUP BY操作。
GROUP BY CUBE(a, b, c)
等效于：
GROUP BY a, b, c UNION ALL
GROUP BY a, b UNION ALL
GROUP BY a, c UNION ALL
GROUP BY b, c UNION ALL
GROUP BY a UNION ALL
GROUP BY b UNION ALL
GROUP BY c UNION ALL
GROUP BY ().
等效于：
GROUPING SETS ((a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ())
ROLLUP(a, b, c)
假设有一个表test，有A、B、C列，如果使用group by grouping sets(A,B,C)，则对(A),(B),(C)进行group by，如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。
GROUP BY GROUPING SETS(a, b, c)
等效于：
GROUP BY a
UNION ALL
GROUP BY b
UNION ALL
GROUP BY c
示例¶
示例1: 将查询结果分组
-- 清理环境
DROP TABLE tab1 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab1 (a INT, b INT, c INT);
INSERT INTO tab1 VALUES(1, 10, 900);
INSERT INTO tab1 VALUES(2, 20, 500);
INSERT INTO tab1 VALUES(2, NULL, 200);
INSERT INTO tab1 VALUES(3, 30, 1000);
SELECT * FROM tab1 ORDER BY a, b, c;
A(int) |B(int) |C(int) |
------------------------------------
1 |10 |900 |
------------------------------------
2 |20 |500 |
------------------------------------
2 |null |200 |
------------------------------------
3 |30 |1000 |
总数目：4
-- 对分组的结果进行判断
SELECT a, COUNT(*) FROM tab1 GROUP BY a ORDER BY a;
A(int) |COUNT(int) |
----------------------------
1 |1 |
----------------------------
2 |2 |
----------------------------
3 |1 |
总数目：3
-- 删除表
DROP TABLE tab1;
示例2: 使用rollup/cube/grouping sets进行查询
-- 清理环境
DROP TABLE tab2 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab2 (deptno INT, job VARCHAR(20), sal REAL);
INSERT INTO tab2 VALUES(10, 'CLERK', 1300);
INSERT INTO tab2 VALUES(10, 'MANAGER', 2450);
INSERT INTO tab2 VALUES(10, 'PRESIDENT', 5000);
INSERT INTO tab2 VALUES(20, 'ANALYST', 6000);
INSERT INTO tab2 VALUES(20, 'CLERK', 1900);
INSERT INTO tab2 VALUES(20, 'MANAGER', 2975);
INSERT INTO tab2 VALUES(30, 'CLERK', 950);
INSERT INTO tab2 VALUES(30, 'MANAGER', 2850);
INSERT INTO tab2 VALUES(30, 'SALESMAN', 5600);
-- 不使用rollup/cube/grouping sets进行查询
SELECT deptno,job,sum(sal) from tab2 group by deptno,job order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |SALESMAN |5600 |
总数目：9
-- rollup语句
SELECT deptno,job,sum(sal) from tab2 group by rollup(deptno,job) order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
10 |null |8750 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
20 |null |10875 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
30 |null |9400 |
--------------------------------------------------
null |null |29025 |
总数目：13
-- cube语句
SELECT deptno,job,sum(sal) from tab2 group by cube(deptno,job) order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
10 |null |8750 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
20 |null |10875 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
30 |null |9400 |
--------------------------------------------------
null |ANALYST |6000 |
--------------------------------------------------
null |CLERK |4150 |
--------------------------------------------------
null |MANAGER |8275 |
--------------------------------------------------
null |PRESIDENT |5000 |
--------------------------------------------------
null |SALESMAN |5600 |
--------------------------------------------------
null |null |29025 |
总数目：18
-- grouping sets语句
SELECT deptno,job,sum(sal) from tab2 group by grouping sets(deptno,job) order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |null |8750 |
--------------------------------------------------
20 |null |10875 |
--------------------------------------------------
30 |null |9400 |
--------------------------------------------------
null |ANALYST |6000 |
--------------------------------------------------
null |CLERK |4150 |
--------------------------------------------------
null |MANAGER |8275 |
--------------------------------------------------
null |PRESIDENT |5000 |
--------------------------------------------------
null |SALESMAN |5600 |
总数目：8
-- grouping sets(rollup())语句
SELECT deptno,job,sum(sal) from tab2 group by grouping sets(rollup(deptno,job)) order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
10 |null |8750 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
20 |null |10875 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
30 |null |9400 |
--------------------------------------------------
null |null |29025 |
总数目：13
SELECT deptno,job,sum(sal) from tab2 group by grouping sets(rollup(deptno,job),job) order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
10 |null |8750 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
20 |null |10875 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
30 |null |9400 |
--------------------------------------------------
null |ANALYST |6000 |
--------------------------------------------------
null |CLERK |4150 |
--------------------------------------------------
null |MANAGER |8275 |
--------------------------------------------------
null |PRESIDENT |5000 |
--------------------------------------------------
null |SALESMAN |5600 |
--------------------------------------------------
null |null |29025 |
总数目：18
-- grouping sets(cube())语句
SELECT deptno,job,sum(sal) from tab2 group by grouping sets(cube(deptno,job)) order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
10 |null |8750 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
20 |null |10875 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
30 |null |9400 |
--------------------------------------------------
null |ANALYST |6000 |
--------------------------------------------------
null |CLERK |4150 |
--------------------------------------------------
null |MANAGER |8275 |
--------------------------------------------------
null |PRESIDENT |5000 |
--------------------------------------------------
null |SALESMAN |5600 |
--------------------------------------------------
null |null |29025 |
总数目：18
SELECT deptno,job,sum(sal) from tab2 group by grouping sets(cube(deptno,job),job) order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
10 |null |8750 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
20 |null |10875 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
30 |null |9400 |
--------------------------------------------------
null |ANALYST |6000 |
--------------------------------------------------
null |ANALYST |6000 |
--------------------------------------------------
null |CLERK |4150 |
--------------------------------------------------
null |CLERK |4150 |
--------------------------------------------------
null |MANAGER |8275 |
--------------------------------------------------
null |MANAGER |8275 |
--------------------------------------------------
null |PRESIDENT |5000 |
--------------------------------------------------
null |PRESIDENT |5000 |
--------------------------------------------------
null |SALESMAN |5600 |
--------------------------------------------------
null |SALESMAN |5600 |
--------------------------------------------------
null |null |29025 |
总数目：23
-- grouping sets, grouping sets语句
SELECT deptno,job,sum(sal) from tab2 group by grouping sets(deptno,job), grouping sets(deptno,job) order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
10 |null |8750 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
20 |null |10875 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
30 |null |9400 |
--------------------------------------------------
null |ANALYST |6000 |
--------------------------------------------------
null |CLERK |4150 |
--------------------------------------------------
null |MANAGER |8275 |
--------------------------------------------------
null |PRESIDENT |5000 |
--------------------------------------------------
null |SALESMAN |5600 |
总数目：26
-- grouping sets, expr语句
SELECT deptno,job,sum(sal) from tab2 group by grouping sets(deptno,job), job order by deptno,job;
DEPTNO(int) |JOB(varchar) |SUM(real) |
--------------------------------------------------
20 |ANALYST |6000 |
--------------------------------------------------
null |ANALYST |6000 |
--------------------------------------------------
10 |CLERK |1300 |
--------------------------------------------------
20 |CLERK |1900 |
--------------------------------------------------
30 |CLERK |950 |
--------------------------------------------------
null |CLERK |4150 |
--------------------------------------------------
10 |MANAGER |2450 |
--------------------------------------------------
20 |MANAGER |2975 |
--------------------------------------------------
30 |MANAGER |2850 |
--------------------------------------------------
null |MANAGER |8275 |
--------------------------------------------------
10 |PRESIDENT |5000 |
--------------------------------------------------
null |PRESIDENT |5000 |
--------------------------------------------------
30 |SALESMAN |5600 |
--------------------------------------------------
null |SALESMAN |5600 |
总数目：14
-- 删除表
DROP TABLE tab2;