《oracle教學(xué)》尚硅谷_宋紅康_05_分組函數(shù)

1、第第5 5節(jié)節(jié)分分 組組 函函 數(shù)數(shù)講師：宋紅康講師：宋紅康 新浪微博：尚硅谷新浪微博：尚硅谷- -宋紅康宋紅康目目 標(biāo)標(biāo)通過(guò)本章學(xué)習(xí)，您將可以: 了解組函數(shù)。 描述組函數(shù)的用途。 使用GROUP BY 子句對(duì)數(shù)據(jù)分組。 使用HAVING 子句過(guò)濾分組結(jié)果集。什么是分組函數(shù)什么是分組函數(shù)分組函數(shù)作用于一組數(shù)據(jù)，并對(duì)一組數(shù)據(jù)返回一個(gè)值。EMPLOYEES表表 EMPLOYEES 中的工資最大值中的工資最大值組函數(shù)類型組函數(shù)類型 AVG COUNT MAX MIN STDDEV SUM張三：10李四：3SELECTcolumn, group_function(column), .FROMtable

2、WHEREconditionGROUP BYcolumnORDER BYcolumn;組函數(shù)語(yǔ)法組函數(shù)語(yǔ)法SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)FROM employeesWHERE job_id LIKE %REP%;AVG（平均值）和平均值）和 SUM （合計(jì)）函數(shù)合計(jì)）函數(shù)可以對(duì)數(shù)值型數(shù)據(jù)數(shù)值型數(shù)據(jù)使用AVG 和 SUM 函數(shù)。MIN（最小值）和最小值）和 MAX（最大值）函數(shù)最大值）函數(shù)可以對(duì)任意數(shù)據(jù)類型任意數(shù)據(jù)類型的數(shù)據(jù)使用 MIN 和 MAX 函數(shù)。SELECT MIN(hire_date), MAX(hi

3、re_date)FROM employees;SELECT COUNT(*)FROM employeesWHERE department_id = 50;COUNT（計(jì)數(shù)）函數(shù)計(jì)數(shù)）函數(shù)COUNT(*) 返回表中記錄總數(shù),適用于任意數(shù)據(jù)類型任意數(shù)據(jù)類型。COUNT（計(jì)數(shù)）函數(shù)計(jì)數(shù)）函數(shù)COUNT(expr) 返回expr不為空不為空的記錄總數(shù)。SELECT COUNT(commission_pct)FROM employeesWHERE department_id = 50;SELECT AVG(commission_pct)FROM employees;組函數(shù)與空值組函數(shù)與空值組函數(shù)忽略空值

4、忽略空值。例如：Select avg(commission_pct),sum(commission_pct)/107,sum(commission_pct)/count(commission_pct)From employees;查看結(jié)果的不同。SELECT AVG(NVL(commission_pct, 0)FROM employees;在組函數(shù)中使用在組函數(shù)中使用NVL函數(shù)函數(shù)NVL函數(shù)使分組函數(shù)無(wú)法忽略空值函數(shù)使分組函數(shù)無(wú)法忽略空值。SELECT COUNT(DISTINCT department_id)FROM employees;DISTINCT 關(guān)鍵字關(guān)鍵字COUNT(DISTIN

5、CT expr)返回expr非空且不重復(fù)非空且不重復(fù)的記錄總數(shù)分組數(shù)據(jù)分組數(shù)據(jù) EMPLOYEES求出求出EMPLOYEES表中各表中各部門的部門的平均工資平均工資440095003500640010033SELECTcolumn, group_function(column)FROMtableWHEREconditionGROUP BYgroup_by_expressionORDER BYcolumn;分組數(shù)據(jù)分組數(shù)據(jù): GROUP BY 子句語(yǔ)法子句語(yǔ)法可以使用可以使用GROUP BY子句將表中的數(shù)據(jù)分成若干組子句將表中的數(shù)據(jù)分成若干組明確：明確：WHERE一定放在一定放在FROM后面后面

6、SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id ;GROUP BY 子句子句 在在SELECT 列表中所有未包含在組函數(shù)中的列都應(yīng)該包含列表中所有未包含在組函數(shù)中的列都應(yīng)該包含在在 GROUP BY 子句中。子句中。GROUP BY 子句子句 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中SELECT AVG(salary)FROM employeesGROUP BY department_id ;使用多個(gè)列分組使用多個(gè)列分組EMPLOYEES使用多個(gè)列使用多個(gè)列進(jìn)行分組進(jìn)行分組SE

7、LECT department_id dept_id, job_id, SUM(salary)FROM employeesGROUP BY department_id, job_id ;在在GROUP BY子句中包含多個(gè)列子句中包含多個(gè)列非法使用組函數(shù)非法使用組函數(shù)所有包含于SELECT 列表中，而未包含于組函數(shù)中的列都必須包含于 GROUP BY 子句中。SELECT department_id, COUNT(last_name)FROM employees;SELECT department_id, COUNT(last_name) *ERROR at line 1:ORA-00937:

8、not a single-group group functionGROUP BY 子句中缺少列非法使用組函數(shù)非法使用組函數(shù) 不能在不能在 WHERE 子句中使用組函數(shù)。子句中使用組函數(shù)。 可以在可以在 HAVING 子句中使用組函數(shù)。子句中使用組函數(shù)。SELECT department_id, AVG(salary)FROM employeesWHERE AVG(salary) 8000GROUP BY department_id;WHERE AVG(salary) 8000 *ERROR at line 3:ORA-00934: group function is not allowed

9、hereWHERE 子句中不能使用組函數(shù)子句中不能使用組函數(shù)過(guò)濾分組過(guò)濾分組EMPLOYEES部門最高工資部門最高工資比比 10000 高的高的部門部門SELECTcolumn, group_functionFROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_conditionORDER BYcolumn;過(guò)濾分組：過(guò)濾分組： HAVING 子句子句使用 HAVING 過(guò)濾分組:1. 行已經(jīng)被分組。2. 使用了組函數(shù)。3. 滿足HAVING 子句中條件的分組將被顯示。HAVING 子句子句SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)10000 ;嵌套組函數(shù)嵌套組函數(shù)顯示各部門平均工資的最大值SELECT MAX(AVG(salary)FROM employeesGROUP BY department_id;SELECTcolumn, group_function(column)FROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_