《oracle教學》尚硅谷_宋紅康_05_分組函數

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

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

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

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

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

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

7、LECT department_id dept_id, job_id, SUM(salary)FROM employeesGROUP BY department_id, job_id ;在在GROUP BY子句中包含多個列子句中包含多個列非法使用組函數非法使用組函數所有包含于SELECT 列表中，而未包含于組函數中的列都必須包含于 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 子句中缺少列非法使用組函數非法使用組函數 不能在不能在 WHERE 子句中使用組函數。子句中使用組函數。 可以在可以在 HAVING 子句中使用組函數。子句中使用組函數。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 子句中不能使用組函數子句中不能使用組函數過濾分組過濾分組EMPLOYEES部門最高工資部門最高工資比比 10000 高的高的部門部門SELECTcolumn, group_functionFROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_conditionORDER BYcolumn;過濾分組：過濾分組： HAVING 子句子句使用 HAVING 過濾分組:1. 行已經被分組。2. 使用了組函數。3. 滿足HAVING 子句中條件的分組將被顯示。HAVING 子句子句SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)10000 ;嵌套組函數嵌套組函數顯示各部門平均工資的最大值SELECT MAX(AVG(salary)FROM employeesGROUP BY department_id;SELECTcolumn, group_function(column)FROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_