Oracle SQL&PLSQL课件：第5章 组函数和分组统计.ppt

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Oracle SQL&PL/SQL,第,5,章 组函数和分组统计,识别可用的组函数,掌握组函数的用法,使用,GROUP BY,子句对数据进行分组,使用,HAVING,子句取得或者排除成组的行,本章要点,什么是组函数,组函数对数据行的集合进行操作并按组给出一个结果,EMP,“,EMP,表中的,最高工资”,DEPTNO SAL,-,10 2450,10 5000,10 1300,20 800,20 1100,20 3000,20 3000,20 2975,30 1600,30 2850,30 1250,30 950,30 1500,30 1250,MAX(SAL),-,5000,组函数的类别,AVG,COUNT,MAX,MIN,STDDEV,SUM,VARIANCE,使用组函数,SELECT,column,group_function(column),FROM,table,WHERE,condition,GROUP BY,column,ORDER BY,column,;,使用组函数,组函数的使用原则：,使用关键字,DISTINCT,可以排除重复值；关键字,ALL,可以包含所有值，包括重复的值；缺省情况下是,ALL。,参数的数据类型可以是,CHAR、VARCHAR2、NUMBER、DATE。,除了,COUNT(*)，,其他所有的组函数都忽略空值，可以使用,NVL,函数将空值转换成一个实际的值。,当使用,GROUP BY,子句时，,Oracle,服务器会自动对结果集合按升序排列。,AVG,和,SUM,的用法,AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL),-,1400 1600 1250 5600,您可以对数值型数据使用,AVG,和,SUM,SQL SELECTAVG(,sal,),MAX(,sal,),2MIN(,sal,),SUM(,sal,),3FROM,emp,4WHEREjob LIKE SALES%;,MIN,和,MAX,的用法,MIN,和,MAX,可以用于任何数据类型,SQL SELECTMIN(,hiredate,),MAX(,hiredate,),2 FROM,emp,;,MIN(HIRED MAX(HIRED,-,17-DEC-80 12-JAN-83,COUNT,的用法,COUNT(*),-,6,SQL SELECTCOUNT(*),2 FROM,emp,3 WHERE,deptno,=30;,COUNT(*),返回表中行的总数,COUNT,的用法,COUNT(,expr,),返回非空行的数量,SQL SELECTCOUNT(,comm,),2 FROM,emp,3 WHERE,deptno,=30;,COUNT(COMM),-,4,COUNT,的用法,ENAME JOB SAL COMM,-,SMITH CLERK 800,ALLEN SALESMAN 1600 300,WARD SALESMAN 1250 500,JONES MANAGER 2975,MARTIN SALESMAN 1250 1400,组函数和空值,组函数会忽略列中的空值,SQL SELECT AVG(,comm,),2 FROM,emp,;,AVG(COMM),-,550,ENAME JOB SAL COMM,-,SMITH CLERK 800,ALLEN SALESMAN 1600 300,WARD,SALESMAN 1250 500,JONES,MANAGER 2975,MARTIN SALESMAN 1250 1400,BLAKE MANAGER 2850,CLARK MANAGER 2450,SCOTT ANALYST 3000,KING PRESIDENT 5000,TURNER SALESMAN 1500 0,ADAMS CLERK 1100,JAMES CLERK 950,FORD ANALYST 3000,MILLER CLERK 1300,在组函数中使用,NVL,函数,NVL,函数可以使组函数强制包含含有空值的记录,SQL SELECT AVG(NVL(,comm,0),2 FROM,emp,;,AVG(NVL(COMM,0),-,157.14286,创建数据组,EMP,“按部门,分组求出,各部门的,平均工资”,2916.6667,2175,1566.6667,DEPTNO SAL,-,10 2450,10 5000,10 1300,20 800,20 1100,20 3000,20 3000,20 2975,30 1600,30 2850,30 1250,30 950,30 1500,30 1250,DEPTNO AVG(SAL),-,10 2916.6667,20 2175,30 1566.6667,用,GROUP BY,子句创建数据组,SELECT,column,group_function(column),FROM,table,WHERE,condition,GROUP BY,group_by_expression,ORDER BY,column,;,通过,GROUP BY,子句将表中的记录划分成若干个小组。,使用,GROUP BY,子句,在,SELECT,列表中除了组函数那些项所有列都必须包含在,GROUP BY,子句中。,SQL SELECT,deptno,AVG(,sal,),2 FROM,emp,3 GROUP BY,deptno,;,DEPTNO AVG(SAL),-,10 2916.6667,20 2175,30 1566.6667,使用,GROUP BY,子句,GROUP BY,所指定的列并不是必须出现在,SELECT,列表中。,SQL SELECT AVG(,sal,),2 FROM,emp,3 GROUP BY,deptno,;,AVG(SAL),-,2916.6667,2175,1566.6667,按多个列分组,EMP,“求出每个部门,内的每个工种,的薪水合计,”,DEPTNO JOB SAL,-,10 MANAGER 2450,10 PRESIDENT 5000,10 CLERK 1300,20 CLERK 800,20 CLERK 1100,20 ANALYST 3000,20 ANALYST 3000,20 MANAGER 2975,30 SALESMAN 1600,30 MANAGER 2850,30 SALESMAN 1250,30 CLERK 950,30 SALESMAN 1500,30 SALESMAN 1250,JOB SUM(SAL),-,CLERK 1300,MANAGER 2450,PRESIDENT 5000,ANALYST 6000,CLERK 1900,MANAGER 2975,CLERK 950,MANAGER 2850,SALESMAN 5600,DEPTNO,-,10,10,10,20,20,20,30,30,30,按多列分组的,GROUP BY,子句,SQL SELECT,deptno,job,sum(,sal,),2 FROM,emp,3 GROUP BY,deptno,job;,DEPTNO JOB SUM(SAL),-,10 CLERK 1300,10 MANAGER 2450,10 PRESIDENT 5000,20 ANALYST 6000,20 CLERK 1900,.,9 rows selected.,使用组函数的非法的查询,如果在查询中使用了组函数，任何不在组函数中的列或表达式都必须包含在,GROUP BY,子句中,。,SQL SELECT,deptno,COUNT(,ename,),2 FROM,emp,;,SELECT,deptno,COUNT(,ename,),*,ERROR at line 1:,ORA-00937:not a single-group group function,GROUP BY,子句中没有相应的列,使用组函数的非法的查询,SQL SELECT,deptno,COUNT(,ename,),2 FROM,emp,3 GROUP BY,deptno,;,DEPTNO COUNT(ENAME),-,10 3,20 5,30 6,通过增加,GROUP BY,子句纠正错误,使用组函数的非法的查询,不能在,WHERE,子句中限制组,可以通过,HAVING,子句限制组,SQL SELECT,deptno,AVG(,sal,),2 FROM,emp,3 WHERE AVG(,sal,)2000,4 GROUP BY,deptno,;,WHERE AVG(,sal,)2000,*,ERROR at line 3:,ORA-00934:group function is not allowed here,不能在,WHERE,子句中限制组,排除组结果,每个组内,最高薪水大于,$2900”,才输出,EMP,5000,3000,2850,DEPTNO SAL,-,10 2450,10 5000,10 1300,20 800,20 1100,20 3000,20 3000,20 2975,30 1600,30 2850,30 1250,30 950,30 1500,30 1250,DEPTNO MAX(SAL),-,10 5000,20 3000,用,HAVING Clause,子句排除组结果,使用,HAVING,子句限制组,记录已经分组,.,使用过组函数,.,与,HAVING,子句匹配的结果才输出,SELECT,column,group_function,FROM,table,WHERE,condition,GROUP BY,group_by_expression,HAVING,group_condition,ORDER BY,column,;,使用,HAVING,子句,SQL SELECT,deptno,max(,sal,),2 FROM,emp,3 GROUP BY,deptno,4 HAVING max(,sal,)2900;,DEPTNO MAX(SAL),-,10 5000,20 3000,使用,HAVING,子句,SQL SELECT job,SUM(,sal,)PAYROLL,2 FROM,emp,3 WHERE job NOT LIKE SALES%,4 GROUP BY job,6 ORDER BY SUM(,sal,);,JOB PAYROLL,-,ANALYST 6000,MANAGER 8275,5,HAVING SUM(,sal,)5000,组函数的嵌套,SQL SELECT max(,avg,(,sal,),2 FROM,emp,3 GROUP BY,deptno,;,MAX(AVG(SAL),-,2916.6667,显示平均薪水的最大值,注：与单行函数不同，组函数只能嵌套两层,