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

五、聚集五、聚集(分组分组)函数函数 目标目标ll完成本节课的学习后，应当完成下面的目标完成本节课的学习后，应当完成下面的目标:识记常用的分组函数。识记常用的分组函数。描述分组函数的用处。描述分组函数的用处。使用使用GROUP BY GROUP BY 子句对元组进行分组子句对元组进行分组使用使用HAVING HAVING 子句来筛选分组子句来筛选分组什么是分组函数什么是分组函数?ll分组函数对一组数据行进行操作，并对每个分组函数对一组数据行进行操作，并对每个组得出一个结果组得出一个结果.EMPEMP“EMP EMP 表中最表中最表中最表中最大的大的大的大的SALSAL值值值值”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 AVG COUNT COUNT MAXMAXMIN MIN STDDEV STDDEV SUMSUMVARIANCEVARIANCE使用分组函数使用分组函数SELECTcolumn,group_function(column)FROMtableWHEREconditionGROUP BYcolumnORDER BYcolumn;使用使用AVGAVG和和SUM SUM 函数函数AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)-1400 1600 1250 5600ll对于数字类型的数据，可以使用对于数字类型的数据，可以使用 AVGAVG和和SUM SUM 函数函数.SQL SELECTAVG(sal),MAX(sal),2MIN(sal),SUM(sal)3FROMemp 4WHEREjob LIKE SALES%;使用使用 MINMIN和和MAX MAX 函数函数ll可以对许多类型的数据使用可以对许多类型的数据使用MINMIN和和MAXMAX函数函数.SQL SELECTMIN(hiredate),MAX(hiredate)2 FROMemp;MIN(HIRED MAX(HIRED-17-DEC-80 12-JAN-83 使用使用 COUNTCOUNT函数函数 COUNT(*)-6SQL SELECTCOUNT(*)2 FROMemp 3 WHEREdeptno=30;llCOUNT(*)COUNT(*)返回查询出的总行数返回查询出的总行数.使用使用 COUNTCOUNT函数函数llCOUNT(COUNT(exprexpr)返回返回exprexpr值非空的行的值非空的行的数数.SQL SELECTCOUNT(comm)2 FROMemp 3 WHEREdeptno=30;COUNT(COMM)-4分组函数和空值分组函数和空值ll分组函数忽略了行中所有的空行分组函数忽略了行中所有的空行.SQL SELECT AVG(comm)2 FROM emp;AVG(COMM)-550分组函数和分组函数和NVLNVL函数的结合函数的结合llNVLNVL函数使得分组函数可以处理空值函数使得分组函数可以处理空值.SQL SELECT AVG(NVL(comm,0)2 FROM emp;AVG(NVL(COMM,0)-157.14286创建聚组数据创建聚组数据 EMPEMP“EMPEMP表中表中表中表中每个部门每个部门每个部门每个部门的平均的平均的平均的平均工资工资工资工资”2916.6667 2916.6667 2175 2175 1566.6667 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.6667GROUPGROUP BY BY 子句子句SELECTcolumn,group_function(column)FROMtableWHEREconditionGROUP BYgroup_by_expressionORDER BYcolumn;ll将表中的数据行用将表中的数据行用GROUPGROUP语句语句分为几个组分为几个组.使用使用 GROUP BYGROUP BY子句子句ll在在SELECTSELECT语句中，没有使用分组函数语句中，没有使用分组函数的列必须在的列必须在GROUP ByGROUP 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 BYGROUP BY子句子句llGROUP BYGROUP BY后面的列可以不出现在后面的列可以不出现在 SELECTSELECT链中链中.SQL SELECT AVG(sal)2 FROM emp 3 GROUP BY deptno;AVG(SAL)-2916.6667 21751566.6667根据多个列进行分组根据多个列进行分组EMPEMP“每个部门中，每个部门中，每个部门中，每个部门中，每种工作的每种工作的每种工作的每种工作的工资总和工资总和工资总和工资总和”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 1250JOB SUM(SAL)-CLERK 1300MANAGER 2450PRESIDENT 5000ANALYST 6000CLERK 1900MANAGER 2975CLERK 950MANAGER 2850SALESMAN 5600DEPTNO-101010202020303030在多个列上使用在多个列上使用 GROUP BY 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.分组函数的误用分组函数的误用ll在在SELECT SELECT 语句中，任何不在聚组函数语句中，任何不在聚组函数中出现的列，必须在中出现的列，必须在 GROUP BY GROUP BY 子句中子句中.SQL SELECTdeptno,COUNT(ename)2 FROMemp;SELECT deptno,COUNT(ename)*ERROR at line 1:ORA-00937:not a single-group group functionDeptnoDeptnoDeptno列没有在列没有在列没有在列没有在列没有在列没有在 GROUP BY GROUP BY GROUP BY 子句中出现子句中出现子句中出现子句中出现子句中出现子句中出现分组函数的误用分组函数的误用不能在不能在 WHEREWHERE子句中对列做出限定子句中对列做出限定.使用使用 HAVING 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 WHERE WHERE 子句对子句对子句对子句对子句对子句对分组做出限定分组做出限定分组做出限定分组做出限定分组做出限定分组做出限定排除聚组结果排除聚组结果每组中薪水大每组中薪水大每组中薪水大每组中薪水大于于于于$2900$2900的员工号的员工号的员工号的员工号”EMPEMP500050003000300028502850 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 HAVING 子句排除分组结果子句排除分组结果ll用用 HAVING HAVING 子句来限定分组子句来限定分组行被分组了行被分组了.使用了分组函数使用了分组函数.符合符合 HAVINGHAVING子句条件的组将被显示出来子句条件的组将被显示出来.SELECTcolumn,group_functionFROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_conditionORDER BYcolumn;使用使用 HAVING 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使用使用 HAVINGHAVING子句子句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 6000MANAGER 82755 HAVING SUM(sal)5000分组函数的嵌套使用分组函数的嵌套使用SQL SELECT max(avg(sal)2 FROM emp 3 GROUP BY deptno;MAX(AVG(SAL)-2916.6667ll显示最大的平均工资显示最大的平均工资.总结总结SELECTcolumn,group_function(column)FROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_conditionORDER BYcolumn;ll子句的顺序子句的顺序:WHERE WHERE 子句子句GROUP BY GROUP BY 子句子句HAVING HAVING 子句子句ORDER BYORDER BY子句子句