对GROUPBY子句的扩展教案.ppt

《对GROUPBY子句的扩展教案.ppt》由会员分享，可在线阅读，更多相关《对GROUPBY子句的扩展教案.ppt（27页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、17-1Copyright Oracle Corporation,2001.All rights reserved.对GROUPBY子句的扩展 Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望17-2Copyright Oracle Corporation,2001.All rights reserved.目标目标通过本章学习，您将可以通过本章学习，您将可以:使用使用 ROLLUP 操作分组操作分组使用使用 CUBE 操作分组操作分组使用使用 GROUPING 函数

2、处理函数处理 ROLLUP 或或 CUBE操作所产操作所产生的空值生的空值使用使用 GROUPING SETS 操作进行单独分组操作进行单独分组 17-3Copyright Oracle Corporation,2001.All rights reserved.组函数组函数组函数处理多行返回一个行组函数处理多行返回一个行SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUP BYgroup_by_expressionORDER BYcolumn;SELECT AVG(salary),STDDEV(salary),COUN

3、T(commission_pct),MAX(hire_date)FROM employeesWHERE job_id LIKE SA%;例子例子例子例子:17-4Copyright Oracle Corporation,2001.All rights reserved.GROUP BY 子句子句SELECT department_id,job_id,SUM(salary),COUNT(employee_id)FROM employeesGROUP BY department_id,job_id;SELECTcolumn,FROMtableWHEREconditionGROUP BYgroup_

4、by_expressionORDER BYcolumn;例子例子例子例子:语法语法语法语法:group_function(column).17-5Copyright Oracle Corporation,2001.All rights reserved.HAVING 子句子句使用使用 HAVING 对组函数进行限制对组函数进行限制对查询进行第二次限制对查询进行第二次限制SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUP BYgroup_by_expressionHAVING having_expression ORD

5、ER BYcolumn;17-6Copyright Oracle Corporation,2001.All rights reserved.带有带有ROLLUP 和和CUBE 操作的操作的GROUP BY 子句子句使用使用带有带有ROLLUP 和和 CUBE 操作的操作的GROUP BY 子句产生子句产生多种分组结果多种分组结果ROLLUP 产生产生n+1种分组结果种分组结果CUBE 产生产生2的的n次方种分组结果次方种分组结果17-7Copyright Oracle Corporation,2001.All rights reserved.ROLLUP 操作符操作符ROLLUP 是对是对 G

6、ROUP BY 子句的扩展子句的扩展ROLLUP 产生产生n+1种分组结果，顺序是从右向左种分组结果，顺序是从右向左 SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUP BYROLLUP group_by_expressionHAVING having_expression;ORDER BYcolumn;17-8Copyright Oracle Corporation,2001.All rights reserved.ROLLUP 应用举例应用举例SELECT department_id,job_id,SUM(sal

7、ary)FROM employees WHERE department_id 60GROUP BY ROLLUP(department_id,job_id);12317-9Copyright Oracle Corporation,2001.All rights reserved.CUBE 操作符操作符CUBE是对是对 GROUP BY 子句的扩展子句的扩展CUBE 会产生类似于笛卡尔集的分组结果会产生类似于笛卡尔集的分组结果 SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUP BYCUBE group_by_expr

8、essionHAVING having_expressionORDER BYcolumn;17-10Copyright Oracle Corporation,2001.All rights reserved.CUBE 应用举例应用举例SELECT department_id,job_id,SUM(salary)FROM employees WHERE department_id 60GROUP BY CUBE(department_id,job_id);123417-11Copyright Oracle Corporation,2001.All rights reserved.GROUPING

9、 函数函数GROUPING 函数可以和函数可以和 CUBE 或或 ROLLUP 结合使用结合使用使用使用 GROUPING 函数，可以找到哪些列在该行中参加函数，可以找到哪些列在该行中参加了分组了分组使用使用 GROUPING 函数函数,可以区分空值产生的原因可以区分空值产生的原因GROUPING 函数返回函数返回 0 或或 1SELECT column,group_function(column).,GROUPING(expr)FROM tableWHERE conditionGROUP BY ROLLUPCUBE group_by_expressionHAVING having_expre

10、ssionORDER BY column;17-12Copyright Oracle Corporation,2001.All rights reserved.GROUPING 函数举例函数举例 SELECT department_id DEPTID,job_id JOB,SUM(salary),GROUPING(department_id)GRP_DEPT,GROUPING(job_id)GRP_JOBFROM employeesWHERE department_id 50GROUP BY ROLLUP(department_id,job_id);21317-13Copyright Orac

11、le Corporation,2001.All rights reserved.GROUPING SETSGROUPING SETS 是对是对GROUP BY 子句的进一步扩充子句的进一步扩充使用使用 GROUPING SETS 在同一个查询中定义多个分组集在同一个查询中定义多个分组集Oracle 对对 GROUPING SETS 子句指定的分组集进行分组后子句指定的分组集进行分组后用用 UNION ALL 操作将各分组结果结合起来操作将各分组结果结合起来 Grouping set 的优点的优点:只进行一次分组即可只进行一次分组即可不必书写复杂的不必书写复杂的 UNION 语句语句GROUPI

12、NG SETS 中包含的分组项越多中包含的分组项越多,性能越好。性能越好。17-14Copyright Oracle Corporation,2001.All rights reserved.GROUPING SETS应用举例应用举例SELECT department_id,job_id,manager_id,avg(salary)FROM employeesGROUP BY GROUPING SETS(department_id,job_id),(job_id,manager_id);1217-15Copyright Oracle Corporation,2001.All rights re

13、served.复合列复合列复合列是被作为整体处理的一组列的集合复合列是被作为整体处理的一组列的集合ROLLUP(a,d)使用括号将若干列组成复合列在使用括号将若干列组成复合列在ROLLUP 或或 CUBE 中作中作为整体进行操作为整体进行操作在在ROLLUP 或或 CUBE中中,复合列可以避免产生不必要的分复合列可以避免产生不必要的分组结果组结果(b,c)(b,c)(b,c)17-16Copyright Oracle Corporation,2001.All rights reserved.复合列应用举例复合列应用举例SELECT department_id,job_id,manager_id

14、,SUM(salary)FROM employees GROUP BY ROLLUP(department_id,(job_id,manager_id);21317-17Copyright Oracle Corporation,2001.All rights reserved.连接分组集连接分组集连接分组集可以产生有用的对分组项的结合连接分组集可以产生有用的对分组项的结合将各分组集将各分组集,ROLLUP 和和 CUBE 用逗号连接用逗号连接 Oracle 自自动在动在 GROUP BY 子句中将各分组集进行连接子句中将各分组集进行连接 连接的结果是对各分组生成笛卡尔集连接的结果是对各分组生成

15、笛卡尔集GROUP BY GROUPING SETS(a,b),GROUPING SETS(c,d)17-18Copyright Oracle Corporation,2001.All rights reserved.连接分组集应用举例连接分组集应用举例SELECT department_id,job_id,manager_id,SUM(salary)FROM employeesGROUP BY department_id,ROLLUP(job_id),CUBE(manager_id);123417-19Copyright Oracle Corporation,2001.All rights reserved.总结总结通过本章学习通过本章学习,您已经可以您已经可以:使用使用 ROLLUP 操作符操作符使用使用 CUBE 操作符操作符使用使用 GROUPING 函数处理在函数处理在 ROLLUP 或或 CUBE中产生的空值中产生的空值使用使用 GROUPING SETS 创建分组集创建分组集在在 GROUP BY 子句中组合分组子句中组合分组:复合列复合列连接分组集连接分组集