oracle数据库sql语句聚集(分组)函数.ppt

五、聚集(分组)函数,目标,完成本节课的学习后，应当完成下面的目标:识记常用的分组函数。描述分组函数的用处。使用GROUPBY子句对元组进行分组使用HAVING子句来筛选分组,什么是分组函数?,分组函数对一组数据行进行操作，并对每个组得出一个结果.,EMP,“EMP表中最大的SAL值”,DEPTNOSAL-1024501050001013002080020110020300020300020297530160030285030125030950301500301250,MAX(SAL)-5000,分组函数的类型,AVGCOUNTMAXMINSTDDEVSUMVARIANCE,使用分组函数,SELECTcolumn,group_function(column)FROMtableWHEREconditionGROUPBYcolumnORDERBYcolumn;,使用AVG和SUM函数,AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)-1400160012505600,对于数字类型的数据，可以使用AVG和SUM函数.,SQLSELECTAVG(sal),MAX(sal),2MIN(sal),SUM(sal)3FROMemp4WHEREjobLIKESALES%;,使用MIN和MAX函数,可以对许多类型的数据使用MIN和MAX函数.,SQLSELECTMIN(hiredate),MAX(hiredate)2FROMemp;,MIN(HIREDMAX(HIRED-17-DEC-8012-JAN-83,使用COUNT函数,COUNT(*)-6,SQLSELECTCOUNT(*)2FROMemp3WHEREdeptno=30;,COUNT(*)返回查询出的总行数.,使用COUNT函数,COUNT(expr)返回expr值非空的行的数.,SQLSELECTCOUNT(comm)2FROMemp3WHEREdeptno=30;,COUNT(COMM)-4,分组函数和空值,分组函数忽略了行中所有的空行.,SQLSELECTAVG(comm)2FROMemp;,AVG(COMM)-550,分组函数和NVL函数的结合,NVL函数使得分组函数可以处理空值.,SQLSELECTAVG(NVL(comm,0)2FROMemp;,AVG(NVL(COMM,0)-157.14286,创建聚组数据,EMP,“EMP表中每个部门的平均工资”,2916.6667,2175,1566.6667,DEPTNOSAL-1024501050001013002080020110020300020300020297530160030285030125030950301500301250,DEPTNOAVG(SAL)-102916.6667202175301566.6667,GROUPBY子句,SELECTcolumn,group_function(column)FROMtableWHEREconditionGROUPBYgroup_by_expressionORDERBYcolumn;,将表中的数据行用GROUP语句分为几个组.,使用GROUPBY子句,在SELECT语句中，没有使用分组函数的列必须在GROUPBy子句中.,SQLSELECTdeptno,AVG(sal)2FROMemp3GROUPBYdeptno;,DEPTNOAVG(SAL)-102916.6667202175301566.6667,使用GROUPBY子句,GROUPBY后面的列可以不出现在SELECT链中.,SQLSELECTAVG(sal)2FROMemp3GROUPBYdeptno;,AVG(SAL)-2916.666721751566.6667,根据多个列进行分组,EMP,“每个部门中，每种工作的工资总和”,DEPTNOJOBSAL-10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250,JOBSUM(SAL)-CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600,DEPTNO-101010202020303030,在多个列上使用GROUPBY子句,SQLSELECTdeptno,job,sum(sal)2FROMemp3GROUPBYdeptno,job;,DEPTNOJOBSUM(SAL)-10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK1900.9rowsselected.,分组函数的误用,在SELECT语句中，任何不在聚组函数中出现的列，必须在GROUPBY子句中.,SQLSELECTdeptno,COUNT(ename)2FROMemp;,SELECTdeptno,COUNT(ename)*ERRORatline1:ORA-00937:notasingle-groupgroupfunction,Deptno列没有在GROUPBY子句中出现,分组函数的误用,不能在WHERE子句中对列做出限定.使用HAVING子句来限定分组.,SQLSELECTdeptno,AVG(sal)2FROMemp3WHEREAVG(sal)20004GROUPBYdeptno;,WHEREAVG(sal)2000*ERRORatline3:ORA-00934:groupfunctionisnotallowedhere,不能用WHERE子句对分组做出限定,排除聚组结果,每组中薪水大于$2900的员工号”,EMP,DEPTNOSAL-1024501050001013002080020110020300020300020297530160030285030125030950301500301250,DEPTNOMAX(SAL)-105000203000,用HAVING子句排除分组结果,用HAVING子句来限定分组行被分组了.使用了分组函数.符合HAVING子句条件的组将被显示出来.,SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGgroup_conditionORDERBYcolumn;,使用HAVING子句,SQLSELECTdeptno,max(sal)2FROMemp3GROUPBYdeptno4HAVINGmax(sal)2900;,DEPTNOMAX(SAL)-105000203000,使用HAVING子句,SQLSELECTjob,SUM(sal)PAYROLL2FROMemp3WHEREjobNOTLIKESALES%4GROUPBYjob6ORDERBYSUM(sal);,JOBPAYROLL-ANALYST6000MANAGER8275,5HAVINGSUM(sal)5000,分组函数的嵌套使用,SQLSELECTmax(avg(sal)2FROMemp3GROUPBYdeptno;,MAX(AVG(SAL)-2916.6667,显示最大的平均工