Oracle數(shù)據(jù)庫(kù)-SQL+PLSQL-Less05-使用分組函數(shù)來(lái)對(duì)數(shù)據(jù)進(jìn)行聚集

使用分組函數(shù)來(lái)對(duì)數(shù)據(jù)進(jìn)行聚集工程目標(biāo)1.統(tǒng)計(jì)每個(gè)部門的平均工資，和最高工資，最低工資，并按照部門號(hào)排序2.查詢出平均工資高于2000的部門以及其平均工資所需知識(shí)點(diǎn)什么是分組函數(shù)使用分組函數(shù)查詢數(shù)據(jù)使用groupby子句查詢數(shù)據(jù)使用having子句過(guò)濾數(shù)據(jù)什么是分組函數(shù)?分組函數(shù)對(duì)一組數(shù)據(jù)行進(jìn)行操作，并對(duì)每個(gè)組得出一個(gè)結(jié)果.EMP“EMP表中最大的SAL值”DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250MAX(SAL)---------5000分組函數(shù)的類型AVG求平均值COUNT統(tǒng)計(jì)行數(shù)MAX求最大值MIN求最小值SUM求和分組函數(shù)根本語(yǔ)法SELECT [column,]group_function(column)FROM table[WHERE condition][GROUPBY column][ORDERBY column];使用AVG和SUM函數(shù)對(duì)于數(shù)字類型的數(shù)據(jù)，可以使用AVG和SUM函數(shù).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函數(shù)可以對(duì)許多類型的數(shù)據(jù)使用MIN和MAX函數(shù).SQL>SELECT MIN(hiredate),MAX(hiredate)2FROM emp;MIN(HIREDMAX(HIRED------------------17-DEC-8012-JAN-83

使用COUNT函數(shù)COUNT(*)返回查詢出的總行數(shù).COUNT(*)---------6SQL>SELECT COUNT(*)2FROM emp3WHERE deptno=30;分組函數(shù)和空值分組函數(shù)忽略了行中所有的空行.SQL>SELECTAVG(comm)2FROMemp;AVG(COMM)---------550分組函數(shù)和NVL函數(shù)的結(jié)合NVL函數(shù)使得分組函數(shù)可以處理空值.SQL>SELECTAVG(NVL(comm,0))2FROMemp;AVG(NVL(COMM,0))----------------157.14286創(chuàng)立聚組數(shù)據(jù)EMP“EMP表中每個(gè)部門的平均工資”2916.666721751566.6667DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250DEPTNOAVG(SAL)----------------102916.6667202175301566.6667創(chuàng)立分組數(shù)據(jù):

GROUPBY子句根本語(yǔ)法將表中的數(shù)據(jù)行用GROUPＢＹ語(yǔ)句分為幾個(gè)組.SELECT column,group_function(column)FROM table[WHERE condition][GROUPBY group_by_expression][ORDERBY column];使用GROUPBY子句在SELECT語(yǔ)句中，沒(méi)有使用分組函數(shù)的列必須在GROUPBY子句中SQL>SELECTdeptno,AVG(sal)2FROMemp3GROUPBYdeptno;DEPTNOAVG(SAL)------------------102916.6667202175301566.6667使用GROUPBY子句GROUPBY后面的列可以不出現(xiàn)在SELECT列中SQL>SELECTAVG(sal)2FROMemp3GROUPBYdeptno;AVG(SAL)---------2916.666721751566.6667根據(jù)多個(gè)列進(jìn)行分組EMP“每個(gè)部門中，每種工作的工資總和”DEPTNOJOBSAL---------------------------10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250JOBSUM(SAL)------------------CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600DEPTNO--------101010202020303030在多個(gè)列上使用GROUPBY子句SQL>SELECTdeptno,job,sum(sal)2FROMemp3GROUPBYdeptno,job;DEPTNOJOBSUM(SAL)---------------------------10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK1900...9rowsselected.分組函數(shù)的誤用在SELECT語(yǔ)句中，任何不在聚組函數(shù)中出現(xiàn)的列，必須在GROUPBY子句中.SQL>SELECT deptno,COUNT(ename)2FROM emp;SELECTdeptno,COUNT(ename)*ERRORatline1:ORA-00937:notasingle-groupgroupfunction分組函數(shù)的誤用不能在WHERE子句中對(duì)列做出限定.使用HAVING子句來(lái)限定分組.SQL>SELECT deptno,AVG(sal)2FROM emp3WHERE AVG(sal)>20004GROUPBY deptno;WHEREAVG(sal)>2000*ERRORatline3:ORA-00934:groupfunctionisnotallowedhere排除聚組結(jié)果組中最高薪水大于$2900的部門號(hào)”EMP500030002850DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250DEPTNOMAX(SAL)------------------105000203000用HAVING子句排除分組結(jié)果用HAVING子句來(lái)限定分組行被分組了.使用了分組函數(shù).符合HAVING子句條件的組將被顯示出來(lái).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分組函數(shù)的嵌套使用顯示最大的平均工資.SQL>SELECTmax(avg(sal))2FROMemp3GROUPBYdeptno;MAX(AVG(SAL))-------------2916.6667練習(xí)案例1.統(tǒng)計(jì)每個(gè)部門的平均工資，和最高工資，最低工資，并按照部門號(hào)排序SQL>selectdeptno,avg(sal)avg_sal,max(sal)max_sal,min(sal)min_salfromempgroupbydeptnoorderbydeptno;DEPTNOAVG_SALMAX_SALMIN_SAL----------------------------------------102916.6666750001300202991