Oracle数据库-SQL+PLSQL-Less05-使用分组函数来对数据进行聚集

使用分组函数来对数据进行聚集工程目标1.统计每个部门的平均工资，和最高工资，最低工资，并按照部门号排序2.查询出平均工资高于2000的部门以及其平均工资所需知识点什么是分组函数使用分组函数查询数据使用groupby子句查询数据使用having子句过滤数据什么是分组函数?分组函数对一组数据行进行操作，并对每个组得出一个结果.EMP“EMP表中最大的SAL值”DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250MAX(SAL)---------5000分组函数的类型AVG求平均值COUNT统计行数MAX求最大值MIN求最小值SUM求和分组函数根本语法SELECT [column,]group_function(column)FROM table[WHERE condition][GROUPBY column][ORDERBY column];使用AVG和SUM函数对于数字类型的数据，可以使用AVG和SUM函数.AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)-----------------------------------1400160012505600SQL>SELECT AVG(sal),MAX(sal),2 MIN(sal),SUM(sal)3 FROM emp4 WHERE jobLIKE'SALES%';

使用MIN和MAX函数可以对许多类型的数据使用MIN和MAX函数.SQL>SELECT MIN(hiredate),MAX(hiredate)2FROM emp;MIN(HIREDMAX(HIRED------------------17-DEC-8012-JAN-83

使用COUNT函数COUNT(*)返回查询出的总行数.COUNT(*)---------6SQL>SELECT COUNT(*)2FROM emp3WHERE deptno=30;分组函数和空值分组函数忽略了行中所有的空行.SQL>SELECTAVG(comm)2FROMemp;AVG(COMM)---------550分组函数和NVL函数的结合NVL函数使得分组函数可以处理空值.SQL>SELECTAVG(NVL(comm,0))2FROMemp;AVG(NVL(COMM,0))----------------157.14286创立聚组数据EMP“EMP表中每个部门的平均工资”2916.666721751566.6667DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250DEPTNOAVG(SAL)----------------102916.6667202175301566.6667创立分组数据:

GROUPBY子句根本语法将表中的数据行用GROUPＢＹ语句分为几个组.SELECT column,group_function(column)FROM table[WHERE condition][GROUPBY group_by_expression][ORDERBY column];使用GROUPBY子句在SELECT语句中，没有使用分组函数的列必须在GROUPBY子句中SQL>SELECTdeptno,AVG(sal)2FROMemp3GROUPBYdeptno;DEPTNOAVG(SAL)------------------102916.6667202175301566.6667使用GROUPBY子句GROUPBY后面的列可以不出现在SELECT列中SQL>SELECTAVG(sal)2FROMemp3GROUPBYdeptno;AVG(SAL)---------2916.666721751566.6667根据多个列进行分组EMP“每个部门中，每种工作的工资总和”DEPTNOJOBSAL---------------------------10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250JOBSUM(SAL)------------------CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600DEPTNO--------101010202020303030在多个列上使用GROUPBY子句SQL>SELECTdeptno,job,sum(sal)2FROMemp3GROUPBYdeptno,job;DEPTNOJOBSUM(SAL)---------------------------10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK1900...9rowsselected.分组函数的误用在SELECT语句中，任何不在聚组函数中出现的列，必须在GROUPBY子句中.SQL>SELECT deptno,COUNT(ename)2FROM emp;SELECTdeptno,COUNT(ename)*ERRORatline1:ORA-00937:notasingle-groupgroupfunction分组函数的误用不能在WHERE子句中对列做出限定.使用HAVING子句来限定分组.SQL>SELECT deptno,AVG(sal)2FROM emp3WHERE AVG(sal)>20004GROUPBY deptno;WHEREAVG(sal)>2000*ERRORatline3:ORA-00934:groupfunctionisnotallowedhere排除聚组结果组中最高薪水大于$2900的部门号”EMP500030002850DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250DEPTNOMAX(SAL)------------------105000203000用HAVING子句排除分组结果用HAVING子句来限定分组行被分组了.使用了分组函数.符合HAVING子句条件的组将被显示出来.SELECT column,group_functionFROM table[WHERE condition][GROUPBY group_by_expression][HAVING group_condition][ORDERBY column];使用HAVING子句SQL>SELECTdeptno,max(sal)2FROMemp3GROUPBYdeptno4HAVINGmax(sal)>2900;DEPTNOMAX(SAL)------------------105000203000分组函数的嵌套使用显示最大的平均工资.SQL>SELECTmax(avg(sal))2FROMemp3GROUPBYdeptno;MAX(AVG(SAL))-------------2916.6667练习案例1.统计每个部门的平均工资，和最高工资，最低工资，并按照部门号排序SQL>selectdeptno,avg(sal)avg_sal,max(sal)max_sal,min(sal)min_salfromempgroupbydeptnoorderbydeptno;DEPTNOAVG_SALMAX_SALMIN_SAL----------------------------------------102916.6666750001300202991