1、第5節(jié)分組函數(shù)講師：宋紅康新浪微博：尚硅谷-宋紅康目標(biāo)通過本章學(xué)習(xí)，您將可以:了解組函數(shù)。描述組函數(shù)的用途。使用GROUP BY 子句對數(shù)據(jù)分組。使用HAVING 子句過濾分組結(jié)果集。什么是分組函數(shù)分組函數(shù)作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個(gè)值。EMPLOYEES表 EMPLOYEES中的工資最大值組函數(shù)類型AVG COUNT MAX MIN STDDEVSUM2.0m李四：2.1m3張三：2.1m10組函數(shù)語法SELECT FROM WHERE GROUP ORDERcolumn,table.condition column column;BY BYgroup_function(column

2、),AVG（平均值）和SUM （合計(jì)）函數(shù)可以對數(shù)值型數(shù)據(jù)使用AVG 和 SUM 函數(shù)。SELECTFROMemployeesWHERE job_id LIKE %REP%;AVG(salary), MAX(salary), MIN(salary), SUM(salary)MIN（最小值）和MAX（最大值）函數(shù)可以對任意數(shù)據(jù)類型的數(shù)據(jù)使用MIN和MAX函數(shù)。SELECT FROMMIN(hire_date), MAX(hire_date) employees;COUNT（計(jì)數(shù)）函數(shù)返回表中記錄總數(shù),適用于任意數(shù)據(jù)類型。COUNT(*)SELECTFROMCOUNT(*)employeesdep

3、artment_id = 50;WHERECOUNT（計(jì)數(shù)）函數(shù)COUNT(expr) 返回expr不為空的記錄總數(shù)。SELECT FROM WHEREemployees department_id = 50;COUNT(commission_pct)組函數(shù)與空值組函數(shù)忽略空值。SELECT FROMemployees;例如：Select avg(commission_pct),sum(commission_pct)/107, sum(commission_pct)/count(commission_pct)From employees;查看結(jié)果的不同。AVG(commission_pct)在

4、組函數(shù)中使用NVL函數(shù)NVL函數(shù)使分組函數(shù)無法忽略空值。SELECT FROMemployees;AVG(NVL(commission_pct, 0)DISTINCT 關(guān)鍵字COUNT(DISTINCT錄總數(shù)expr)返回expr非空且不重復(fù)的記SELECT FROMCOUNT(DISTINCT department_id) employees;分組數(shù)據(jù)EMPLOYEES44009500求出EMPLOYEES表中各 部門的 平均工資3500640010033分組數(shù)據(jù):可以使用GROUPBY 子句語法GROUPBY子句將表中的數(shù)據(jù)分成若干組SELECT FROM WHEREcolumn, gro

5、up_function(column) tableconditionORDERBYcolumn;明確：WHERE一定放在FROM后面GROUP BYgroup_by_expressionGROUP BY 子句列表中所有未包含在組函數(shù)中的列都應(yīng)該包含BY 子句中。在SELECT在GROUPSELECTFROMdepartment_id,)employeesGROUP BYdepartment_id;AVG(salaryGROUP BY 子句包含在GROUP BY 子句中的列不必包含在SELECT 列表中SELECT FROMAVG(salary) employeesGROUP BYdepartm

6、ent_id;使用多個(gè)列分組EMPLOYEES使用多個(gè)列進(jìn)行分組在GROUPBY子句中包含多個(gè)列SELECT FROMdepartment_id dept_id, job_id, SUM(salary) employees;GROUP BY department_id, job_id非法使用組函數(shù)所有包含于SELECT列表中，而未包含于組函數(shù)中的列都必須包含于子句中。GROUPBYSELECT FROMdepartment_id, employees;COUNT(last_name)GROUPBY 子句中缺少列SELECT department_id, COUNT(last_name)*ERR

7、OR at line 1:ORA-00937: not a single-group group function非法使用組函數(shù)不能在可以在WHERE子句中使用組函數(shù)。HAVING子句中使用組函數(shù)。SELECT FROM WHEREdepartment_id, AVG(salary) employeesAVG(salary) 8000 department_id;GROUPBYWHEREAVG(salary) 8000*at line 3:ERRORORA-00934: group function isnot allowedhereWHERE 子句中不能使用組函數(shù)過濾分組EMPLOYEES部

8、門最高工資 10000 高的部門過濾分組： HAVING 子句使用 HAVING 過濾分組:1. 行已經(jīng)被分組。2. 使用了組函數(shù)。3. 滿足HAVING 子句中條件的分組將被顯示。SELECT FROM WHERE GROUPcolumn, group_function tablecondition group_by_expressionBYORDERBYcolumn;HAVINGgroup_conditionHAVING 子句SELECT FROM GROUP BY HAVINGdepartment_id, MAX(salary) employeesdepartment_idMAX(salary)10000;嵌套組函數(shù)顯示各部門平均工資的最大值SELECT FROMemployeesGROUP BY department_id;MAX(AVG(salary)總結(jié)通過本章學(xué)習(xí)，您已經(jīng)學(xué)會(huì): 使用組函數(shù):avg(),sum(),max(),min(),count()在查詢中使用在查詢中使用BY 子句。GROUPH