oracle数据库ppt+中科院培训专用Les17_cn.ppt

1、,增强 GROUP BY 子句,目标,完成本课后, 您应当能够： 用 ROLLUP 操作产生小计值 用 CUBE 操作产生交叉表值 用 GROUPING 函数确定由 ROLLUP 或 CUBE 创建的行值 用 GROUPING SETS 产生一个单个的结果集,组函数的回顾,组函数在行集上操作，给出分组结果,SELECTcolumn, group_function(column). . . FROMtable WHEREcondition GROUP BYgroup_by_expression ORDER BYcolumn;,SELECT AVG(salary), STDDEV(salary),

2、 COUNT(commission_pct),MAX(hire_date) FROM employees WHERE job_id LIKE SA%;,例如：,GROUP BY 子句的回顾,SELECT department_id, job_id, SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id ;,SELECTcolumn, FROMtable WHEREcondition GROUP BYgroup_by_expression ORDER BYcolumn;,例子：,语法：,grou

3、p_function(column). . .,HAVING 子句的回顾,用 HAVING 子句指定将被显示的那些组 在限制条件的基础上可以进一步约束分组,SELECTcolumn, group_function(column). FROMtable WHEREcondition GROUP BYgroup_by_expression HAVING having_expression ORDER BYcolumn;,带 ROLLUP 或 CUBE 运算的 GROUP BY,带 ROLLUP 或 CUBE 的 GROUP BY 子句用交叉引用列产生超合计行 ROLLUP 分组产生一个包含常规分组

4、行和小计值的结果集 CUBE 分组产生一个包含 ROLLUP 行和交叉表行的结果集,ROLLUP 操作,ROLLUP 是一个 GROUP BY 子句的扩展 用 ROLLUP 操作产生小计和累计,SELECTcolumn, group_function(column). . . FROMtable WHEREcondition GROUP BYROLLUP group_by_expression HAVING having_expression; ORDER BYcolumn;,ROLLUP 操作的例子,1,2,3,CUBE 操作,CUBE 是 GROUP BY 子句的扩展 能够用 CUBE 操

5、作产生带单个 SELECT 语句的交叉表值,SELECTcolumn, group_function(column). FROMtable WHEREcondition GROUP BYCUBE group_by_expression HAVING having_expression ORDER BYcolumn;,CUBE 操作的例子,SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id 60 GROUP BY CUBE (department_id, job_id) ;,1,2,3,4,GR

6、OUPING 函数,GROUPING 函数既可以用于 CUBE 操作，也可以用于 ROLLUP 操作 用 GROUPING 函数模拟能够发现在一行中的构成小计的分组 用 GROUPING 函数，你能够从 ROLLUP 或 CUBE 创建的空值中区分存储的 NULL 值 GROUPING 函数返回 0 或 1,SELECT column, group_function(column), GROUPING(expr) FROM table WHERE condition GROUP BY ROLLUPCUBE group_by_expression HAVING having_expression

7、 ORDER BY column;,GROUPING 函数：例子,SELECT department_id DEPTID, job_id JOB, SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOB FROM employees WHERE department_id 50 GROUP BY ROLLUP(department_id, job_id);,2,1,3,分组集,GROUPING SETS 是 GROUP BY 子句更进一步的扩展 你能够用 GROUPING SETS 在同一查询中定义多个分组

8、 Oracle 服务器计算在 GROUPING SETS 子句中指定的所有分组；用 UNION ALL 操作组合单个的分组结果 分组集合的效率： 对基表仅进行一次查询 不需要写复杂的 UNION 语句 GROUPING SETS 有更多的元素，更好的执行性能,Hidden Slide,GROUPING SETS: 例子,SELECT department_id, job_id, manager_id,avg(salary) FROM employees GROUP BY GROUPING SETS (department_id,job_id), (job_id,manager_id);,1,2

9、,Hidden Slide,复合列,复合列是一个作为整体被处理的列集合 ROLLUP (a, , d) 为了指定复合列，用 GROUP BY 子句来分组在圆括号内的列，因此， Oracle 服务器在进行 ROLLUP 或 CUBE 操作时将它们作为一个整体来处理 当使用 ROLLUP 或 CUBE 时，复合列将跳过在确定级别上的聚合,(b,c),Hidden Slide,复合列: 例子,SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY ROLLUP( department_id,(job_

10、id, manager_id);,2,1,3,Hidden Slide,连接分组,连接分组提供一种简明的方式来生成有用的分组组合 为了指定连接分组集合，用逗号分开多重分组集合， ROLLUP，和 CUBE 操作，以便 Oracle 服务器将它们组合在一个单个的 GROUP BY 子句中 分组是每个分组集合交叉乘积的结果,GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d),连接分组例子,SELECT department_id, job_id, manager_id, SUM(salary) FROM employeesGROUP BY department_id, ROLLUP(job_id), CUBE(manager_id);,1,2,3,4,小结,在本课中, 您应该已经学会如何： 用 ROLLUP 操作产生小计值 用 CUBE 操作产生交叉表值 用 GROUPING 函数指定由 ROLLUP 或 CUBE 创建的行值 用 GROUPING SETS 语法定义在同一查询中的多重分组 用 GROUP BY 子句以不同的方式组合表达式：