第5章分組函數(shù)

1、Oracle-SQLOracle-SQL開發(fā)開發(fā)-分組函數(shù)分組函數(shù)V1.0V1.0章節(jié)目標(biāo)章節(jié)目標(biāo)通過本章學(xué)習(xí)，學(xué)員應(yīng)達(dá)到如下目標(biāo)：理解單行函數(shù)和分組函數(shù)的區(qū)別；掌握5個(gè)常用分組函數(shù)；掌握分組 GROUP BY語句；掌握過濾分組后的記錄 HAVING 語句；掌握SELECT語句6個(gè)子句的執(zhí)行順序；利用分組函數(shù)和分組語句能解決常見問題;本章內(nèi)容本章內(nèi)容為什么使用分組函數(shù)請思考如下問題？查詢所有員工的每個(gè)月工資總和，平均工資？查詢工資最高和最低的工資是多少？查詢公司的總?cè)藬?shù)？查詢有獎(jiǎng)金的總?cè)藬?shù)？.為什么使用分組函數(shù)分組函數(shù)是對數(shù)據(jù)行的集合進(jìn)行操作并按組給出一個(gè)結(jié)果，這個(gè)結(jié)果可直接輸出，或者用來做判

2、斷條件。 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單行函數(shù)和分組函數(shù)區(qū)別為什么使用分組函數(shù)分組函數(shù)概述分組函數(shù)分組函數(shù)是對表中一組記錄進(jìn)行操作，每組只返回一個(gè)結(jié)果，即首先要對表記錄進(jìn)行分組，然后再進(jìn)行操作匯總，每組返回一個(gè)結(jié)果，分組時(shí)可能是整個(gè)表分為一組，也可能根據(jù)條件分成多組。分組函數(shù)常用到以下五個(gè)函數(shù)：MINMAXSUMAVGCOUNT使用分組函數(shù)SELEC

3、Tcolumn, group_function(column)FROMtableWHEREconditionGROUP BYcolumnHAVING group_function(column)expression ORDER BYcolumn| group_function(column)expression;分組函數(shù)概述分組函數(shù)MIN函數(shù)和MAX函數(shù) MIN和MAX函數(shù)主要是返回每組的最小值和最大值。MIN(DISTINCT|ALL column|expression)MAX(DISTINCT|ALL column|expression)MIN和MAX可以用于任何數(shù)據(jù)類型查詢?nèi)肼毴掌谧钤绾?/p>

4、最晚的日期 SQL SELECTMIN(hiredate), MAX(hiredate) 2 FROMemp;MIN(HIRED MAX(HIRED- -17-DEC-80 12-JAN-83分組函數(shù)SUM函數(shù)和AVG函數(shù)SUM和AVG函數(shù)分別返回每組的總和及平均值。SUM(DISTINCT|ALL column|expression)AVG(DISTINCT|ALL column|expression)SUM和AVG函數(shù)都是只能夠?qū)?shù)值類型的列或表達(dá)式操作。查詢職位以SALES開頭的所有員工平均工資、最低工資、最高工資、工資和。AVG(SAL) MAX(SAL) MIN(SAL) SUM(S

5、AL)- - - - 1400 1600 1250 5600SQL SELECTAVG(sal), MAX(sal), 2MIN(sal), SUM(sal) 3FROMemp 4WHEREjob LIKE SALES%;分組函數(shù)COUNT函數(shù)COUNT函數(shù)的主要功能是返回滿足條件的每組記錄條數(shù)。COUNT( *|DISTINCT|ALL column|expression) COUNT(*)- 6SQL SELECTCOUNT(*) 2 FROMemp 3 WHEREdeptno = 30;COUNT(*)：返回表中滿足條件的行記錄數(shù)查詢部門30有多少個(gè)員工分組函數(shù)COUNT( DISTIN

6、CT|ALL column|expression)：返回滿足條件的非空(NULL)行的數(shù)量查詢部門30有多少個(gè)員工領(lǐng)取獎(jiǎng)金。SQL SELECTCOUNT(comm) 2 FROMemp 3 WHEREdeptno = 30;COUNT(COMM)- 4COUNT函數(shù)分組函數(shù)組函數(shù)中DISTINCTDISTINCT會(huì)消除重復(fù)記錄后再使用組函數(shù) 查詢有員工的部門數(shù)量。 SQL SELECTCOUNT(DISTINCT deptno) 2 FROMemp;SQL SELECT AVG(comm) 2 FROM emp;分組函數(shù)分組函數(shù)中空值處理除了COUNT（*）之外，其它所有分組函數(shù)都會(huì)忽略列中

7、的空值，然后再進(jìn)行計(jì)算。 AVG(COMM)- 550在分組函數(shù)中使用在分組函數(shù)中使用NVLNVL函數(shù)函數(shù)NVL NVL 函數(shù)可以使分組函數(shù)強(qiáng)制包含含有空值的函數(shù)可以使分組函數(shù)強(qiáng)制包含含有空值的記錄記錄SQL SELECT AVG(NVL(comm,0) 2 FROM emp;AVG(NVL(COMM,0)- 157.14286分組函數(shù)練習(xí)練習(xí)1 11.查詢部門20的員工，每個(gè)月的工資總和及平均工資。2.查詢工作在CHICAGO的員工人數(shù)，最高工資及最低工資。3.查詢員工表中一共有幾種崗位類型。思考思考查詢每個(gè)部門的平均工資？創(chuàng)建數(shù)據(jù)組創(chuàng)建數(shù)據(jù)組 DEPTNO SAL- - 10 2450 1

8、0 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解決上述問題，需要按照部門進(jìn)行分組用用GROUP BYGROUP BY子句創(chuàng)建數(shù)據(jù)組子句創(chuàng)建數(shù)據(jù)組SELECTcolumn, group_function(column)FROMtableWHEREconditionGROUP BYgroup_by_expressionORDER BYcol

9、umn;通過 GROUP BY 子句可將表中滿足WHERE條件的記錄按照指定的列劃分成若干個(gè)小組其中GROUP BY子句指定要分組的列使用使用 GROUP BY GROUP BY 子句子句 查詢每個(gè)部門的編號，平均工資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列表中除了分組函數(shù)那些項(xiàng)，所有列都必須包含在GROUP BY 子句中。SQL SELECT

10、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 子句子句 GROUP BY 所指定的列并不是必須出現(xiàn)在SELECT 列表中。SQL SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno; AVG(SAL)- 2916.6667 21751566.6667按多個(gè)列分組按多個(gè)列分組 DEPTNO JOB SAL- - - 10 MANAGER 2450 10 PRESI

11、DENT 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 285

12、0SALESMAN 5600DEPTNO-101010202020303030查詢每個(gè)部門每個(gè)崗位的工資總和。按多列分組的按多列分組的GROUP BYGROUP 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.查詢每個(gè)部門每個(gè)崗位的工資總和。使用分組函數(shù)的非法

13、的查詢使用分組函數(shù)的非法的查詢?nèi)绻诓樵冎惺褂昧私M函數(shù)，任何不在組函數(shù)中的列或表達(dá)式都必須包含在GROUP BY子句中 。SQL SELECTdeptno, COUNT(ename) 2 FROMemp;SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group function使用分組函數(shù)的非法的查詢使用分組函數(shù)的非法的查詢SQL SELECTdeptno, COUNT(ename) 2 FROM emp 3 GROUP BY deptno; DEPTNO COUNT(ENAME)- - 1

14、0 3 20 5 30 6通過增加GROUP BY子句糾正錯(cuò)誤練習(xí)練習(xí)2 21.查詢每個(gè)部門的部門編號，部門名稱，部門人數(shù)，最高工資，最低工資，工資總和，平均工資。2.查詢每個(gè)部門，每個(gè)崗位的部門編號，部門名稱，崗位名稱，部門人數(shù)，最高工資，最低工資，工資總和，平均工資。3.查詢每個(gè)經(jīng)理所管理的人數(shù)，經(jīng)理編號，經(jīng)理姓名，要求包括沒有經(jīng)理的人員信息。排除組結(jié)果排除組結(jié)果 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 150

15、0 30 1250 DEPTNO MAX(SAL)- - 10 5000 20 3000使用組函數(shù)的非法的查詢使用組函數(shù)的非法的查詢不能在 WHERE子句中限制組可以通過 HAVING 子句限制組SQL SELECT deptno, max(sal) 2 FROM emp 3 WHERE max(sal) 2900 4 GROUP BY deptno;WHERE AVG(sal) 2000 *ERROR at line 3:ORA-00934: group function is not allowed here用用 HAVING Clause HAVING Clause子句排除組結(jié)果子句排除

16、組結(jié)果使用 HAVING 子句限制組記錄已經(jīng)分組.使用過組函數(shù).與 HAVING 子句匹配的結(jié)果才輸出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 30

17、00查詢每個(gè)部門最高工資大于2900的部門編號，最高工資使用使用 HAVING 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 6000MANAGER 82755 HAVING SUM(sal)5000SELECTSELECT語句執(zhí)行過程語句執(zhí)行過程通過案例解釋SELECT語句的執(zhí)行過程。SQL SELECT deptno,job,avg(sal) 2 FROM e

18、mp 3 WHERE job in (SALESMAN,MANAGER,CLERK) 4 GROUP BY deptno,job 5 HAVING avg(sal)1000 6 ORDER BY 3 DESC;DEPTNO JOB AVG(SAL)- - - 20MANAGER 297530 MANAGER 285010MANAGER 245030SALESMAN 140010CLERK 1300 SELECTSELECT語句執(zhí)行過程語句執(zhí)行過程SELECTSELECT語句執(zhí)行過程語句執(zhí)行過程SELECTSELECT語句執(zhí)行過程語句執(zhí)行過程SELECT語句執(zhí)行過程：1.通過FROM子句中找到需

19、要查詢的表；2.通過WHERE子句進(jìn)行非分組函數(shù)篩選判斷；3.通過GROUP BY子句完成分組操作；4.通過HAVING子句完成組函數(shù)篩選判斷；5.通過SELECT子句選擇顯示的列或表達(dá)式及組函數(shù)；6.通過ORDER BY子句進(jìn)行排序操作。組函數(shù)和多表連接組函數(shù)和多表連接查詢每個(gè)部門的部門編號、部門名稱、部門人數(shù)SQL SELECT e.deptno,d.dname,count(empno) 2 FROM emp e,dept d 3 WHERE e.deptno = d.deptno 4 GROUP BY e.deptno;上述語句錯(cuò)在哪里？練習(xí)練習(xí)3 31.查詢部門人數(shù)大于2的部門編號，部門