第6章oracle分组函数.ppt

第六章 分组函数 东软IT人才实训中心 本章要点 分组函数的目的 分组函数基本概念 SUM、AVG、MIN、MAX、COUNT函数使用 GROUP BY子句 HAVING子句 分组函数概念 分组函数是对表中一组记录进行操作，每组只返回一个结果。即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果。分组时可能是整个表分为一组，也可能根据条件分成多组。 分组函数常用到以下的五个函数： MIN MAX SUM AVG COUNT MIN函数和MAX函数 MIN和MAX函数主要是返回每组的最小值和最大值。 MIN([DISTINCT|ALL]表达式) MAX([DISTINCT|ALL]表达式) 例6-1 员工最低工资及最高工资的示例。 MIN函数和MAX函数示例 例6-2 员工姓最开始及最后的示例。 例6-3 员工最低工资及最高工资的示例 。 SUM函数和AVG函数 SUM和AVG函数分别返回总和及平均值。 SUM([DISTINCT|ALL]n) AVG([DISTINCT|ALL]n) SUM和AVG函数都是只能够对数字类型的列或表达式操作。 例6-4 公司员工总工资及平均工资的示例。 COUNT函数 COUNT函数的主要功能是返回每组记录的条数。 COUNT({*|[DISTINCT|ALL]表达式}) 例6-5 公司IT_PROG职位的员工人数的示例。 例6-6 公司有部门员工人数的示例。 组函数中DISTINCT DISTINCT会消除重复记录后再使用组函数 例6-7 公司有员工部门数的示例。 组函数中空值处理 所有组函数对空值都是忽略的。 例6-8 员工平均奖金的示例——忽略空值。 例6-9 员工平均奖金的示例——空值转化。 GROUP BY子句 组函数忽略空值,可以使用NVL,NVL2,COALESCE 函数处理空值 结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句 GROUP BY子句示例 例6-10 每个部门的总工资。 例6-11 相同职位且经理相同的员工平均工资。 GROUP BY子句注意问题 在GROUP BY子句使用中，有两点需要注意： GROUP BY子句后的列可以不在SELECT语句中出现。 SELECT子句中出现的非分组函数列必须在GROUP BY子句中出现。 例6-12 查询公司每个职位的平均工资，职位列不显示，同时结果按照平均工资排序。 GROUP BY子句注意问题示例 例6-13 分组汇总错误示例 例6-14 分组汇总正确示例 HAVING子句 例6-15 组函数筛选示例。 原因是Oracle查询语句的执行顺序是： FROM WHERE GROUP BY SELECT ORDER BY HAVING子句 语法结构如下： HAVING子句 例6-16 组函数筛选示例。 HAVING子句 总结SELECT语句执行过程： 通过FROM子句中找到需要查询的表； 通过WHERE子句进行非分组函数筛选判断； 通过GROUP BY子句完成分组操作； 通过HAVING子句完成组函数筛选判断； 通过SELECT子句选择显示的列或表达式及组函数； 通过ORDER BY子句进行排序操作。 HAVING子句 例6-17 组函数演示。 组函数的嵌套 组函数可以实现嵌套操作，嵌套级数是2级。 例6-18 组函数嵌套演示。 本章小结 MIN函数和MAX函数 SUM函数和AVG函数 COUNT函数 组函数中DISTINCT消除重复行 组函数中空值处理 通过GROUP BY子句进行分组汇总 GROUP BY子句使用需要注意的两个问题 HAVING子句的使用 组函数的嵌套 练习 1.查询部门平均工资在8000元以上的部门名称及平均工资。 2.查询员工工作编号中不是以“SA_”开头并且平均工资在8000元以上的工作编号及平均工资，并按平均工资 降序排序。 3.查询部门人数在4人以上的部门的部门名称及最低工 资和最高工资。 4.查询工作不为SA_REP，工资的和大于等于25000的工作编号和每种工作工资的和。 5.显示经理号码，这个经理所管理员工的最低工资，不包括经理号为空的，不包括最低工资小于3000的，按最低工资由高到低排序。 任何时候如果要在SELECT语句中要同时包含个体项（DEPTNO）和组函数（COUNT），则必须在GROUP BY子句中指定这些个体项（DEPTNO）。如果GROUP BY子句缺失，则会返回一个错误 “ORA-00937: 非单组分组函数 ”，同时会在缺失的列名称下方以星号“*”标示出。您可以通过在上面的例子中增加 GROUP B