ORACLE学习笔记系列（15）使用扩展的 GROUP BY 子句
GROUP BY 可选项什么时候可以无：非分组查询中，聚合函数实际上等于将表中所有记录作为一个组来运算。此时在 select列表中指定的列只能是包含聚组函数，不能包含数据表本身的列。比如求所有员工的总工资:SELECT SUM(SALARY) FROM EMP;什么时候必须有：在分组查询中，聚合函数是将数据按分组关键字分组，然后对每一组的函数自变量中的内容进行聚合运算。Select 子句字段可以是分组关键字（group by 后面字段）和聚合函数。 比如求各个部门员工总工资:SELECT DEPTNAME, SUM(SALARY) FROM EMP GROUP BY EMP.DEPTNAME;注意：a.如果没有group by 子句，select 列表中不允许出现字段与分组函数混用的情况。b.在带有group by子句的查询语句中，在select列表中指定的列要么是group by 子句中指定的列，要么包含聚组函数。 出现在select列表中的字段，如果不是包含在分组函数中，那么该字段必须同时出在Group by子句中。c.group by后面字段的顺序不同分组结果不同。
?
?
/*--设置输出格式SET linesize 1000;SET pagesize 25;COLUMN deptno format A10;COLUMN DEPTNAME format A10;COLUMN job format A6;COLUMN NAME format A25; COLUMN lev format A10; */-- 建表：EMPcreate table EMP( id NUMBER(4), deptno VARCHAR2(50), deptname VARCHAR2(50), job VARCHAR2(50), name VARCHAR2(50), lev VARCHAR2(50), salary NUMBER(16,2)); -- EMP表数据SQL> SELECT * FROM EMP; ID DEPTNO DEPTNAME JOB NAME LEV SALARY---------- ---------- ---------- ------ ------------------------- ---------- ---------- 1 BUS PRE James Smith 2000 2 SAL MGR Ron Johnson 875 3 SAL WOR Fred Hobbs 350 4 SUP MGR Susan Jones 500 5 SAL WOR Rob Green 875 6 SUP WOR Jane Brown 500 7 SUP MGR John Grey 662.5 8 SUP WOR Jean Blue 275 9 SUP WOR Henry Heyson 312.5 10 OPE MGR Kevin Black 562.5 11 OPE MGR Keith Long 412.5 12 OPE WOR Frank Howard 312.5 13 OPE WOR Doreen Penn 362.5 14 BUS MGR Mark Smith 387.5 15 BUS MGR Jill Jones 437.5 16 OPE ENG Megan Craig 612.5 17 SUP TEC Matthew Brant 287.5 18 OPE MGR Tony Clerke 500 19 BUS MGR Tanya Conway 500 20 OPE MGR Terry Cliff 537.5 21 SAL MGR Steve Green 687.5 22 SAL MGR Roy Red 937.5 ID DEPTNO DEPTNAME JOB NAME LEV SALARY---------- ---------- ---------- ------ ------------------------- ---------- ---------- 23 SAL MGR Sandra Smith 837.5 24 SAL MGR Gail Silver 562.5 25 SAL MGR Gerald Gold 612.5 26 SAL MGR Eileen Lane 587.5 27 SAL MGR Doreen Upton 587.5 28 SAL MGR Jack Ewing 587.5 29 SAL MGR Paul Owens 612.5 30 SAL MGR Melanie York 637.5 31 SAL MGR Tracy Yellow 562.5 32 SAL MGR Sarah White 587.5 33 SAL MGR Terry Iron 562.5 34 SAL MGR Christine Brown 617.5 35 SAL MGR John Brown 622.5 36 SAL MGR Kelvin Trenton 637.5 37 BUS WOR Damon Jones 700已选择37行。---- GROUP BY 的结果SQL> SELECT DEPTNAME, SUM(SALARY) FROM EMP GROUP BY DEPTNAME;DEPTNAME SUM(SALARY)---------- -----------SUP 2537.5SAL 12340BUS 4025OPE 3300SQL>
?
1、使用 ROLLUP子句 ROLLUP 是GROUP BY子句的扩展，它是为每一个分组返回一条合计记录，并为全部分组返回总计。 ROLLUP 可以按单列进行分组，与可以向 ROLLUP 传递多列。 ROLLUP 按照多列进行分组时，Rollup 后面跟了n个字段，就将进行n+1次分组，从左到右每次减少一个字段进行分组； 然后进行union操作。 例如 ROLLUP(A, B, C)，则执行顺序如下： （1）首先对(A、B、C)进行GROUP BY， （2）然后对(A、B)进行GROUP BY， （3）然后是(A)进行GROUP BY， （4）然后对全表进行GROUP BY, （5）最后将以上四步的结果进行union操作。 示例：-- ROLLUP一个字段：SQL> SELECT DEPTNAME, SUM(SALARY) FROM EMP GROUP BY ROLLUP(DEPTNAME);DEPTNAME SUM(SALARY)---------- -----------BUS 4025OPE 3300SAL 12340SUP 2537.5 22202.5-- 等价于SQL> SELECT DEPTNAME, SUM(SALARY) FROM EMP GROUP BY DEPTNAME 2 UNION 3 SELECT '', SUM(SALARY) FROM EMP;DEPTNAME SUM(SALARY)---------- -----------BUS 4025OPE 3300SAL 12340SUP 2537.5 22202.5-- ROLLUP两个字段：SQL> SELECT DEPTNAME, JOB, SUM(SALARY) FROM EMP GROUP BY ROLLUP(DEPTNAME, JOB);DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS MGR 1325BUS PRE 2000BUS WOR 700BUS 4025OPE ENG 612.5OPE MGR 2012.5OPE WOR 675OPE 3300SAL MGR 11115SAL WOR 1225SAL 12340SUP MGR 1162.5SUP TEC 287.5SUP WOR 1087.5SUP 2537.5 22202.5已选择16行。-- 等价于SQL> SELECT DEPTNAME, JOB, SUM(SALARY) FROM EMP GROUP BY DEPTNAME, JOB 2 UNION 3 SELECT DEPTNAME,'', SUM(SALARY) FROM EMP GROUP BY DEPTNAME 4 UNION 5 SELECT '','', SUM(SALARY) FROM EMP ;DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS MGR 1325BUS PRE 2000BUS WOR 700BUS 4025OPE ENG 612.5OPE MGR 2012.5OPE WOR 675OPE 3300SAL MGR 11115SAL WOR 1225SAL 12340SUP MGR 1162.5SUP TEC 287.5SUP WOR 1087.5SUP 2537.5 22202.5已选择16行。SQL>--合计行的分组列会显示为空，可进行转换SQL> SELECT NVL(DEPTNAME, '合计') DEPTNAME, SUM(SALARY) FROM EMP GROUP BY ROLLUP(DEPTNAME);DEPTNAME SUM(SALARY)---------- -----------BUS 4025OPE 3300SAL 12340SUP 2537.5合计 22202.5SQL> SELECT NVL(DEPTNAME, '合计') DEPTNAME, NVL(JOB, '小计') JOB, SUM(SALARY) FROM EMP GROUP BY ROLLUP(DEPTNAME, JOB);DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS MGR 1325BUS PRE 2000BUS WOR 700BUS 小计 4025OPE ENG 612.5OPE MGR 2012.5OPE WOR 675OPE 小计 3300SAL MGR 11115SAL WOR 1225SAL 小计 12340SUP MGR 1162.5SUP TEC 287.5SUP WOR 1087.5SUP 小计 2537.5合计 小计 22202.5已选择16行。SQL>
?
2、使用 CUBE子句 CUBE子句也是对GROUP BY子句进行扩展，返回CUBE中所有列组合的小计信息，同时在最后显示总计信息。CUBE这里的使用与ROLLUP基本相同，但CUBE的合计更加详细，它能够显示出分组字段的合计信息。 在Group By 中使用Cube可以产生Rollup结果集+和多维度的交叉表数据源。Cube 后面跟了n个字段，就将进行2的N次方的分组运算，然后进行union；例如 GROUP BY CUBE(A, B, C)，则执行顺序如下： （1）首先会对 (A、B、C) 进行GROUP BY， （2）然后是对 (A、B) 进行GROUP BY， （3）然后是对 (A、C) 进行GROUP BY， （4）然后是对 (A) 进行GROUP BY， （5）然后是对 (B、C) 进行GROUP BY， （6）然后是对 (B) 进行GROUP BY， （7）然后是对 (C) 进行GROUP BY， （8）然后对全表进行GROUP BY, （9）最后将以上的结果进行union操作。 -- GROUP BY CUBE 一个字段时，与ROLLUP一个字段，是等价的。SELECT DEPTNAME, SUM(SALARY) FROM EMP GROUP BY ROLLUP(DEPTNAME);SELECT DEPTNAME, SUM(SALARY) FROM EMP GROUP BY CUBE(DEPTNAME) ORDER BY DEPTNAME;--等价于SELECT DEPTNAME, SUM(SALARY) FROM EMP GROUP BY DEPTNAMEUNIONSELECT '', SUM(SALARY) FROM EMP; -- GROUP BY CUBE 两个字段时SELECT DEPTNAME, JOB, SUM(SALARY) FROM EMP GROUP BY CUBE(DEPTNAME, JOB) ORDER BY DEPTNAME;--等价于SELECT DEPTNAME, JOB, SUM(SALARY) FROM EMP GROUP BY DEPTNAME, JOB UNIONSELECT DEPTNAME,'', SUM(SALARY) FROM EMP GROUP BY DEPTNAME UNIONSELECT '', JOB, SUM(SALARY) FROM EMP GROUP BY JOB UNIONSELECT '','', SUM(SALARY) FROM EMP ;-- GROUP BY CUBE 两个字段时SQL> SELECT DEPTNAME, JOB, SUM(SALARY) FROM EMP GROUP BY CUBE(DEPTNAME, JOB) ORDER BY DEPTNAME;DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS MGR 1325BUS PRE 2000BUS WOR 700BUS 4025OPE ENG 612.5OPE MGR 2012.5OPE WOR 675OPE 3300SAL MGR 11115SAL WOR 1225SAL 12340SUP MGR 1162.5SUP TEC 287.5SUP WOR 1087.5SUP 2537.5 ENG 612.5 MGR 15615 PRE 2000 TEC 287.5 WOR 3687.5 22202.5已选择21行。SQL> SELECT NVL(DEPTNAME, '合计') DEPTNAME, NVL(JOB, '小计') JOB, SUM(SALARY) FROM EMP GROUP BY CUBE(DEPTNAME, JOB) ORDER BY DEPTNAME;DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS 小计 4025BUS MGR 1325BUS PRE 2000BUS WOR 700OPE 小计 3300OPE MGR 2012.5OPE WOR 675OPE ENG 612.5SAL MGR 11115SAL WOR 1225SAL 小计 12340SUP TEC 287.5SUP WOR 1087.5SUP MGR 1162.5SUP 小计 2537.5合计 ENG 612.5合计 小计 22202.5合计 MGR 15615合计 PRE 2000合计 TEC 287.5合计 WOR 3687.5已选择21行。SQL>
?
3、使用 GROUPING()函数GROUPING()可接受一个列值，当列值为空时，函数返回1；如果列值非空，则返回0。（注意：GROUPING()只能在ROLLUP、CUBE查询中使用。还可以结合DECODE（）函数来注释使用更好）示例： --GROUPING()与ROLLUP结合使用SQL> SELECT GROUPING(DEPTNAME), DEPTNAME, SUM(SALARY) 2 FROM EMP 3 GROUP BY ROLLUP(DEPTNAME);GROUPING(DEPTNAME) DEPTNAME SUM(SALARY)------------------ ---------- ----------- 0 BUS 4025 0 OPE 3300 0 SAL 12340 0 SUP 2537.5 1 22202.5--使用DECODE（）函数来转换：SQL> SELECT DECODE(GROUPING(DEPTNAME), 1, '合计', DEPTNAME) AS DEPTNAME, 2 DEPTNAME, 3 SUM(SALARY) 4 FROM EMP 5 GROUP BY ROLLUP(DEPTNAME);DEPTNAME DEPTNAME SUM(SALARY)---------- ---------- -----------BUS BUS 4025OPE OPE 3300SAL SAL 12340SUP SUP 2537.5合计 22202.5--GROUPING()与CUBE结合使用SQL> SELECT DECODE(GROUPING(DEPTNAME), 1, '合计', DEPTNAME) AS DEPTNAME, 2 DECODE(GROUPING(JOB), 1, '小计', JOB) AS JOB, 3 SUM(SALARY) 4 FROM EMP 5 GROUP BY CUBE(DEPTNAME, JOB) 6 ORDER BY DEPTNAME, JOB;DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS MGR 1325BUS PRE 2000BUS WOR 700BUS 小计 4025OPE ENG 612.5OPE MGR 2012.5OPE WOR 675OPE 小计 3300SAL MGR 11115SAL WOR 1225SAL 小计 12340SUP MGR 1162.5SUP TEC 287.5SUP WOR 1087.5SUP 小计 2537.5合计 ENG 612.5合计 MGR 15615合计 PRE 2000合计 TEC 287.5合计 WOR 3687.5合计 小计 22202.5已选择21行。SQL>
?
4、使用 GROUPING SETS子句下面使用 GROUPING SETS子句来限制只返回小计记录。 也可以使用 GROUPING SETS子句 来代替多次UNION。GROUPING SETS子句可以按单一字段分组，也可以按组合字段分组。例如 按单一字段分组GROUP BY GROUPING SETS(A, B)，则执行顺序如下： （1）首先是对 (A) 进行GROUP BY， （2）然后是对 (B) 进行GROUP BY， （3）最后将以上的结果进行union操作。 例如 按组合字段分组GROUP BY GROUPING SETS((A, B),(C,D))，则执行顺序如下： （1）首先是对 (A,B) 进行GROUP BY， （2）然后是对 (C,D) 进行GROUP BY， （3）最后将以上的结果进行union操作。 -- 按单一字段分组SQL> SELECT DEPTNAME, JOB, SUM(SALARY) 2 FROM EMP 3 GROUP BY GROUPING SETS(DEPTNAME, JOB) 4 ORDER BY DEPTNAME, JOB;DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS 4025OPE 3300SAL 12340SUP 2537.5 ENG 612.5 MGR 15615 PRE 2000 TEC 287.5 WOR 3687.5已选择9行。SQL>--等价于 SQL> SELECT DEPTNAME, '' AS JOB, SUM(SALARY) FROM EMP GROUP BY DEPTNAME 2 UNION 3 SELECT '' AS DEPTNAME, JOB, SUM(SALARY) FROM EMP GROUP BY JOB;DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS 4025OPE 3300SAL 12340SUP 2537.5 ENG 612.5 MGR 15615 PRE 2000 TEC 287.5 WOR 3687.5已选择9行。SQL>
?
5、使用 GROUPING_ID()函数 GROUPING_ID（）函数可接受一列或多列，它返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是按照顺序对每一列调用GROUPING（）函数的结果组合起来。它的作用是借助HAVING子句对记录进行过滤，将不包含小计或总计的记录除去。GROUPING位向量的十进制值，我们由前面的介绍已知道当GROUPING（）的列值为空时它返回1，当非空时返回0；比如：GROUPING_ID(DIVISION_ID, JOB_ID)division_id,job_id两列都为非空， GROUPING（）都返回0。将这两列的值组合起来，形成一个位向量00，十进制为0。division_id列为非空,job_id列为空，GROUPING（）都返回1。将这两列的值组合起来，形成一个位向量01，十进制为1。division_id列为空,job_id列为非空，GROUPING（）都返回2。将这两列的值组合起来，形成一个位向量10，十进制为2。division_id,job_id两列都为空， GROUPING（）都返回3。将这两列的值组合起来，形成一个位向量11，十进制为3。（这里要特别注意division_id与job_id两列的顺序，如果顺序不一样返回的结果值可能不一样。）示例：SQL> SELECT DEPTNAME, 2 JOB, 3 GROUPING(DEPTNAME) AS DEP, 4 GROUPING(JOB) AS J, 5 GROUPING_ID(DEPTNAME, JOB) AS GRP_ID, 6 SUM(SALARY) 7 FROM EMP 8 GROUP BY CUBE(DEPTNAME, JOB) 9 ORDER BY DEPTNAME, JOB;DEPTNAME JOB DEP J GRP_ID SUM(SALARY)---------- ------ ---------- ---------- ---------- -----------BUS MGR 0 0 0 1325BUS PRE 0 0 0 2000BUS WOR 0 0 0 700BUS 0 1 1 4025OPE ENG 0 0 0 612.5OPE MGR 0 0 0 2012.5OPE WOR 0 0 0 675OPE 0 1 1 3300SAL MGR 0 0 0 11115SAL WOR 0 0 0 1225SAL 0 1 1 12340SUP MGR 0 0 0 1162.5SUP TEC 0 0 0 287.5SUP WOR 0 0 0 1087.5SUP 0 1 1 2537.5 ENG 1 0 2 612.5 MGR 1 0 2 15615 PRE 1 0 2 2000 TEC 1 0 2 287.5 WOR 1 0 2 3687.5 1 1 3 22202.5已选择21行。--使用GROUPING_ID过滤不包含小计或总计的记录：SQL> SELECT DEPTNAME, 2 JOB, 3 GROUPING(DEPTNAME) AS DEP, 4 GROUPING(JOB) AS J, 5 GROUPING_ID(DEPTNAME, JOB) AS GRP_ID, 6 SUM(SALARY) 7 FROM EMP 8 GROUP BY CUBE(DEPTNAME, JOB) 9 HAVING GROUPING_ID(DEPTNAME, JOB) > 0 10 ORDER BY DEPTNAME, JOB;DEPTNAME JOB DEP J GRP_ID SUM(SALARY)---------- ------ ---------- ---------- ---------- -----------BUS 0 1 1 4025OPE 0 1 1 3300SAL 0 1 1 12340SUP 0 1 1 2537.5 ENG 1 0 2 612.5 MGR 1 0 2 15615 PRE 1 0 2 2000 TEC 1 0 2 287.5 WOR 1 0 2 3687.5 1 1 3 22202.5已选择10行。SQL>--使用GROUPING_ID来进行合计的注注释SQL> SELECT DECODE(GROUPING_ID(DEPTNAME, JOB), 3, '总合计', 2, '合计', DEPTNAME) AS DEPTNAME, 2 DECODE(GROUPING_ID(DEPTNAME, JOB), 3, '总合计', 1, '小计', JOB) AS JOB, 3 SUM(SALARY) 4 FROM EMP 5 GROUP BY CUBE(DEPTNAME, JOB) 6 ORDER BY DEPTNAME, JOB;DEPTNAME JOB SUM(SALARY)---------- ------ -----------BUS MGR 1325BUS PRE 2000BUS WOR 700BUS 小计 4025OPE ENG 612.5OPE MGR 2012.5OPE WOR 675OPE 小计 3300SAL MGR 11115SAL WOR 1225SAL 小计 12340SUP MGR 1162.5SUP TEC 287.5SUP WOR 1087.5SUP 小计 2537.5合计 ENG 612.5合计 MGR 15615合计 PRE 2000合计 TEC 287.5合计 WOR 3687.5总合计 总合计 22202.5已选择21行。SQL>
?
6、使用 GROUP_ID()函数 在 GROUP BY 子句中可以多次使用某个列，这样可以实现对数据的重新组织，或是按照不同的数据分组进行统计。 如下面这个查询中包含一个 GROUP BY 子句，其中使用了两次 DEPTNAME 列，第一次是对 DEPTNAME 进行分组， 第二次是在 ROLLOUP 中使用。从结果可以看到最后4行与前面4行是重复的。 这种现象可以通过使用 GROUP_ID() 函数来消除。 GROUP_ID()函数用于消除 GROUP BY 子句返回的重复记录。 GROUP_ID()函数不接收任何参数。 如果某个特定的分组重复出现 N 次，那么 GROUP_ID() 函数返回从 0 到 N-1 之间的一个整数。 -- GROUP BY 子句返回重复记录 SQL> SELECT DEPTNAME, JOB, GROUP_ID(), SUM(SALARY) 2 FROM EMP 3 GROUP BY DEPTNAME, ROLLUP(DEPTNAME, JOB);DEPTNAME JOB GROUP_ID() SUM(SALARY)---------- ------ ---------- -----------BUS MGR 0 1325BUS PRE 0 2000BUS WOR 0 700OPE ENG 0 612.5OPE MGR 0 2012.5OPE WOR 0 675SAL MGR 0 11115SAL WOR 0 1225SUP MGR 0 1162.5SUP TEC 0 287.5SUP WOR 0 1087.5BUS 0 4025OPE 0 3300SAL 0 12340SUP 0 2537.5BUS 1 4025OPE 1 3300SAL 1 12340SUP 1 2537.5已选择19行。SQL>在上面的例子中，除了最后 4 条记录之外，GROUP_ID()函数在其他记录上返回值都是0；最后4条记录与前面4条记录重复，因此GROUP_ID()函数返回1。可使用 HAVING 子句消除重复记录，只返回GROUP_ID()函数等于0的记录。SQL> SELECT DEPTNAME, JOB, GROUP_ID(), SUM(SALARY) 2 FROM EMP 3 GROUP BY DEPTNAME, ROLLUP(DEPTNAME, JOB) 4 HAVING GROUP_ID() = 0;DEPTNAME JOB GROUP_ID() SUM(SALARY)---------- ------ ---------- -----------BUS MGR 0 1325BUS PRE 0 2000BUS WOR 0 700OPE ENG 0 612.5OPE MGR 0 2012.5OPE WOR 0 675SAL MGR 0 11115SAL WOR 0 1225SUP MGR 0 1162.5SUP TEC 0 287.5SUP WOR 0 1087.5BUS 0 4025OPE 0 3300SAL 0 12340SUP 0 2537.5已选择15行。SQL>
?
?
?
?
