Oracle公司内部数据库培训资料Les05_chinese.ppt

分组函数 目标 通过本章学习 您将可以 了解组函数 描述组函数的用途 使用GROUPBY字句数据分组 使用HAVING字句过滤分组结果集 什么是分组函数 分组函数作用于一组数据 并对一组数据返回一个值 EMPLOYEES 表EMPLOYEES中的工资最大值 组函数类型 AVGCOUNTMAXMINSTDDEVSUM SELECT column group function column FROMtable WHEREcondition GROUPBYcolumn ORDERBYcolumn 组函数语法 SELECTAVG salary MAX salary MIN salary SUM salary FROMemployeesWHEREjob idLIKE REP AVG 平均值 和SUM 合计 函数 可以对数值型数据使用AVG和SUM函数 MIN 最小值 和MAX 最大值 函数 可以对任意数据类型的数据使用MIN和MAX函数 SELECTMIN hire date MAX hire date FROMemployees SELECTCOUNT FROMemployeesWHEREdepartment id 50 COUNT 计数 函数 COUNT 返回表中记录总数 COUNT 计数 函数 COUNT expr 返回expr不为空的记录总数 SELECTCOUNT commission pct FROMemployeesWHEREdepartment id 80 SELECTCOUNT DISTINCTdepartment id FROMemployees DISTINCT关键字 COUNT DISTINCTexpr 返回expr非空且不重复的记录总数 SELECTAVG commission pct FROMemployees 组函数与空值 组函数忽略空值 SELECTAVG NVL commission pct 0 FROMemployees 在组函数中使用NVL函数 NVL函数使分组函数无法忽略空值 分组数据 EMPLOYEES 求出EMPLOYEES表中各部门的平均工资 4400 9500 3500 6400 10033 SELECTcolumn group function column FROMtable WHEREcondition GROUPBYgroup by expression ORDERBYcolumn 分组数据 GROUPBY子句语法 可以使用GROUPBY子句将表中的数据分成若干组 SELECTdepartment id AVG salary FROMemployeesGROUPBYdepartment id GROUPBY子句 在SELECT列表中所有未包含在组函数中的列都应该包含在GROUPBY子句中 GROUPBY子句 包含在GROUPBY子句中的列不必包含在SELECT列表中 SELECTAVG salary FROMemployeesGROUPBYdepartment id 使用多个列分组 EMPLOYEES 使用多个列进行分组 SELECTdepartment iddept id job id SUM salary FROMemployeesGROUPBYdepartment id job id 在GROUPBY子句中包含多个列 非法使用组函数 所用包含于SELECT列表中 而未包含于组函数中的列都必须包含于GROUPBY子句中 SELECTdepartment id COUNT last name FROMemployees SELECTdepartment id COUNT last name ERRORatline1 ORA 00937 notasingle groupgroupfunction GROUPBY子句中缺少列 非法使用组函数 不能在WHERE子句中使用组函数 可以在HAVING子句中使用组函数 SELECTdepartment id AVG salary FROMemployeesWHEREAVG salary 8000GROUPBYdepartment id WHEREAVG salary 8000 ERRORatline3 ORA 00934 groupfunctionisnotallowedhere WHERE子句中不能使用组函数 过滤分组 Themaximumsalaryperdepartmentwhenitisgreaterthan 10 000 EMPLOYEES SELECTcolumn group functionFROMtable WHEREcondition GROUPBYgroup by expression HAVINGgroup condition ORDERBYcolumn 过滤分组 HAVING子句 使用HAVING过滤分组 1 行已经被分组 2 使用了组函数 3 满足HAVING子句中条件的分组将被显示 HAVING子句 SELECTdepartment id MAX salary FROMemployeesGROUPBYdepartment idHAVINGMAX salary 10000 SELECTjob id SUM salary PAYROLLFROMemployeesWHEREjob idNOTLIKE REP GROUPBYjob idHAVINGSUM salary 13000ORDERBYSUM salary HAVING子句 嵌套组函数 显示平均工资的最大值 SELECTMAX AVG salary FROMemployeesGROUPBYdepartment id SELECTcolumn group function column FROMtable WHE