oracle sql教程用組函數(shù)合計(jì)數(shù)據(jù)

1、幻燈片1進(jìn)度表：時(shí)間354075分鐘分鐘分鐘講演練習(xí)總共用組函數(shù)合計(jì)數(shù)據(jù)幻燈片2課程目標(biāo)本課進(jìn)一步講解函數(shù)。本課的重點(diǎn)是關(guān)于獲得行的概要信息，例如平均值。課程中將怎樣聚合表中的行到較小的集合中，怎樣指定用于行聚合的搜索條件。目標(biāo)完成本課后, 您應(yīng)當(dāng)能夠執(zhí)行下列操作：識(shí)別可用的組函數(shù)描述組函數(shù)的使用用 GROUP BY 子句分組數(shù)據(jù)用 HAVING 子句包含或排除分組的行5-2幻燈片 3組函數(shù)不象單行函數(shù)，組函數(shù)對行的集合進(jìn)行操作，對每組給出一個(gè)結(jié)果。這些集合可能是整個(gè)表或者是表分成的組。組函數(shù)?組函數(shù)操作行集，給出每組的結(jié)果EMPLOYEES在 EMPLOYEES表中的最高薪水5-3幻燈片 4

2、組函數(shù) (續(xù))每個(gè)函數(shù)接收一個(gè)參數(shù)，下面的表確定你可以在語法中使用的選項(xiàng)：函數(shù)說明AVG(DISTINCT|ALLn)n 的平均值，忽略空值COUNT(*|DISTINCT|ALLexpr)行數(shù)，expr 求除了空計(jì)算(用 * 計(jì)數(shù)所有行，包括重復(fù)和帶空值的行)MAX(DISTINCT|ALLexpr)expr 的最大值，忽略空值MIN(DISTINCT|ALLexpr)expr 的最小值，忽略空值STDDEV(DISTINCT|ALLx)n 的標(biāo)準(zhǔn)差，忽略空值SUM(DISTINCT|ALLn)合計(jì) n 的值，忽略空值VARIANCE(DISTINCT|ALLx)n 的方差，忽略空值組函數(shù)的

3、類型AVG 平均值COUNT 計(jì)數(shù)MAX 最大值MIN 最小值STDDEV 標(biāo)準(zhǔn)差SUM 合計(jì)VARIANCE 方差5-4幻燈片5使用組函數(shù)的原則DISTINCT使得函數(shù)只考慮不重復(fù)的值；ALL 使得函數(shù)考慮每個(gè)值，包括重復(fù)值。默認(rèn)值是 ALL ，因此不需要指定。用于函數(shù)的參數(shù)的數(shù)據(jù)類型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。所有組函數(shù)忽略空值。為了用一個(gè)值代替空值，用 NVL、NVL2 或 COALESCE函數(shù)。當(dāng)使用 GROUP BY 子句時(shí)，Oracle 服務(wù)器隱式以升序排序結(jié)果集。為了覆蓋該默認(rèn)順序，DESC 可以被用于 ORDER BY 子句。教師注釋強(qiáng)調(diào)用

4、DISTINCT 和組函數(shù)忽略空值。ALL 是默認(rèn)。組函數(shù)的語法5-5SELECTcolumn,.FROMtableWHEREconditionGROUP BYcolumnORDER BYcolumn;group function(column),幻燈片 6組函數(shù)你可以使用AVG、SUM、MIN 和 MAX 函數(shù)用于數(shù)字?jǐn)?shù)據(jù)，幻燈片中例子顯示平均、最高、最低和月薪水的和。使用 AVG 和 SUM 函數(shù)你可以使用 AVG 和 SUM 用于數(shù)字?jǐn)?shù)據(jù)5-6SELECTAVG(salary), MAX(salary), MIN(salary), SUM(salary)FROMemployeesWHER

5、E job_id LIKE %REP%;幻燈片 7組函數(shù) (續(xù))你可以用MAX 和 MIN 函數(shù)于任意數(shù)據(jù)類型?；脽羝械睦语@示最年少的和做年老的雇員。下面的例子顯示按字母排序，雇員的排在最前面和最后面的名字。SELECT MIN(last_name), MAX(last_name)FROMemployees;注：AVG、SUM、VARIANCE 和 STDDEV 函數(shù)只能被用于數(shù)字?jǐn)?shù)據(jù)類型。使用 MIN 和 MAX 函數(shù)你可以使用 MIN 和 MAX 用于任何數(shù)據(jù)類型5-7SELECTMIN(hire_date), MAX(hire_date)FROMemployees;幻燈片8COUNT

6、 函數(shù)COUNT 函數(shù)有三中格式：COUNT(*)COUNT(expr) COUNT(DISTINCT expr)COUNT(*) 返回表中滿足 SELECT 語句標(biāo)準(zhǔn)的行數(shù)，包括重復(fù)行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 語句中，COUNT(*) 返回滿足 WHERE 子句條件的行數(shù)。對比，COUNT(expr) 返回在列中的由 expr 指定的非空值的數(shù)。 COUNT(DISTINCT expr) 返回在列中的由 expr 指定的唯一的非空值的數(shù)?；脽羝睦语@示部門 50 中的雇員人數(shù)。使用 COUNT 函數(shù)COUNT(*) 返回一個(gè)表中的行數(shù)5-8SELECT

7、COUNT(*)FROMemployeesWHERE department_id = 50;幻燈片 9COUNT 函數(shù)(續(xù))幻燈片的離子顯示部門 80 中有傭金的雇員人數(shù)。例顯示 EMPLOYEES 表中的部門數(shù)。SELECT COUNT(department_id)FROMemployees;使用 COUNT 函數(shù)COUNT(expr) 返回對于表達(dá)式 expr 非空值的行數(shù)顯示 EMPLOYEES 表中部門數(shù)的值，不包括空值5-9SELECTCOUNT(commis_pct)FROMemployeesWHERE department_id = 80;幻燈片10DISTINCT關(guān)鍵字使用 D

8、ISTINCT 關(guān)鍵字計(jì)算在一列中的重復(fù)值。幻燈片中的例子顯示 EMPLOYEES 表中不重復(fù)的部門數(shù)。使用 DISTINCT 關(guān)鍵字COUNT(DISTINCT expr) 返回對于表達(dá)式 expr 非空并且值不相同的行數(shù)顯示 EMPLOYEES 表中不同部門數(shù)的值5-10SELECTCOUNT(DISTINCT department_id)FROMemployees;幻燈片11組函數(shù)和空值所有組函數(shù)忽略列中的空值。在幻燈片的例子中，平均值只基于表中的那些COMMIS_PCT 列的值有效的行的計(jì)算。平均值計(jì)算是用付給所有雇員的總傭金除以接受傭金的雇員數(shù) (4)。組函數(shù)和 Null 值組函數(shù)忽

9、略列中的空值5-11SELECTAVG(commis_pct)FROMemployees;幻燈片12組函數(shù)和空值(續(xù))NVL 函數(shù)強(qiáng)制組函數(shù)包括空值。在幻燈片的例子中，平均值被基于所有表中的行來計(jì)算，不管 COMMIS_PCT 列是否為空。平均值的計(jì)算是用付給所有雇員的總傭金除以公司的雇員總數(shù) (20)。在組函數(shù)中使用 NVL 函數(shù)NVL 函數(shù)強(qiáng)制組函數(shù)包含空值5-12SELECTAVG(NVL(commispct, 0)FROMemployees;幻燈片13創(chuàng)建數(shù)據(jù)組直到現(xiàn)在，所有組函數(shù)是將表作為一個(gè)大的信息組進(jìn)行處理，有時(shí)，你需要將表的信息劃分為較小的組，可以用 GROUP BY 子句實(shí)現(xiàn)

10、。創(chuàng)建數(shù)據(jù)組EMPLOYEES44009500在EMPLOYEES3500 表中每個(gè)部門的6400 平均薪水100335-13幻燈片14GROUP BY子句你可以用 GROUP BY 子句把表中的行劃分為組。然后你可以用組函數(shù)返回每一組的摘要信息。在語法中：group_by_expres組的依據(jù)。指定那些用于將行分組的列，這些列的值作為行分原則如果在 SELECT 子句中包含了組函數(shù)，就不能選擇單獨(dú)的結(jié)果，除非單獨(dú)的列出現(xiàn)在 GROUP BY 子句中。如果你未能在 GROUP BY 子句中包含一個(gè)字段列表，你會(huì)收到一個(gè)錯(cuò)誤信息。使用 WHERE 子句，你可以在劃分行成組以前過濾行。在在GROU

11、P BYGROUP BY子句中必須包含列。子句中你不能用列別名。默認(rèn)情況下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用ORDER BY 子句覆蓋這個(gè)默認(rèn)值。創(chuàng)建數(shù)據(jù)組: GROUP BY 子句語法用 GROUP BY 子句劃分表中的行到較小的組中5-14SELECTcolumn, group_function(column)FROMtableWHEREconditionGROUP BYgroup_by_expresORDER BYcolumn;幻燈片15GROUP BY當(dāng)使用子句 (續(xù))GROUP BY 子句時(shí)，確保在 SELECT 列表中的所有沒有包括在組函數(shù)中的列必須在

12、 GROUP BY 子句中?；脽羝械睦语@示每個(gè)部門的部門號(hào)和薪水的平均值。下面是包含一個(gè) GROUP BY 子句 SELECT 語句的求值過程：SELECT 子句指定要返回的列：在EMPLOYEES 表中的部門號(hào)你在 GROUP BY 子句中指定分組的所有薪水的平均值FROM 子句指定數(shù)據(jù)庫必須的表：EMPLOYEES 表。WHERE 子句指定被返回的行。因?yàn)闊o WHERE 子句默認(rèn)情況下所有行被返回。GROUP BY 子句指定行怎樣被分組。行用部門號(hào)分組，所以 AVG 函數(shù)被應(yīng)用于薪水列，以計(jì)算每個(gè)部門的平均薪水。教師注釋分組結(jié)果被以分組列隱式排序，可以用 ORDER BY 指定不同的排

13、序順序，但只能用組函數(shù)或分組列。使用 GROUP BY 子句在 SELECT 列表中的不在組函數(shù)中的所有列必須在GROUP BY 子句中5-15SELECTdepartment_id, FROMemployeesGROUP BY department_id;AVG(salary)幻燈片16GROUP BYGROUP BY子句 (續(xù))列不必在 SELECT 子句中，例如，幻燈片中的 SELECT 語句顯示每個(gè)部門的平均薪水，而沒有顯示的部門號(hào)，但是結(jié)果卻是以部門號(hào)分組的。你可以在 ORDER BY 子句中使用組函數(shù)。SELECT FROM GROUPORDERdepartment_id, AVG

14、(salary)employeesBY department_idBY AVG(salary);教師注釋示范在 SELECT 語句中用和不用 DEPARTMENT_ID 列查詢。本頁和上頁對比。使用 GROUP BY 子句GROUP BY 列不必在 SELECT 列表中5-16SELECTAVG(salary) FROMemployeesGROUP BY department_id;幻燈片17在組中分組有時(shí)你需要看在組中分組的結(jié)果。幻燈片展示一個(gè)顯示在每個(gè)部門中付給每個(gè)工作崗位的合計(jì)薪水的。EMPLOYEES 表先用部門號(hào)分組，在分組中再用工作崗位分組。例如，在部門 50中，4 個(gè) stock

15、 clerks 被分組在一起，并且對在分組中的所有 stock clerks 產(chǎn)生一個(gè)單個(gè)的結(jié)果 (合計(jì)薪水)。多于一個(gè)列的分組EMPLOYEES在 EMPLOYEES表中先按照部門，再按工作崗位分組相加薪水5-17幻燈片18在組中分組(續(xù))你可以列出多個(gè) GROUP BY 列返回組和子組的摘要結(jié)果。你可以用 GROUP BY子句中的列的順序確定結(jié)果的默認(rèn)排序順序。下面是幻燈片的 SELECT 語句中包含一個(gè) GROUP BY 子句時(shí)的求值過程：SELECT 子句指定被返回的列：部門號(hào)在 EMPLOYEES 表中Job ID 在 EMPLOYEES 表中你在 GROUP BY 子句中指定的組中

16、所有薪水的合計(jì)FROM 子句指定數(shù)據(jù)庫必須的表：EMPLOYEES 表。GROUP BY 子句指定你怎樣分組行：首先，用部門號(hào)分組行。第二，在部門號(hào)的分組中再用 job ID 分組行。如此 SUM 函數(shù)被用于每個(gè)部門號(hào)分組中的所有 job ID 的 salary 列。在多列上使用 GROUP BY 子句5-18SELECTdepartment_id dept_id, job_id, SUM(salary) FROMemployeesGROUP BY department_id, job_id;幻燈片19規(guī)定使用Group 函數(shù)的查詢無論何時(shí)，你在同一個(gè) SELECT 語句中使用單獨(dú)的列 (DE

17、PARTMENT_ID) 和組函數(shù) (COUNT) 的混合時(shí)，你必須包括一個(gè) GROUP BY 子句來指定單獨(dú)的列 (在本例中，DEPARTMENT_ID)。如果缺少 GROUP BY，將會(huì)出現(xiàn)錯(cuò)誤信息 “not a single-group group function”，并且一個(gè)星號(hào) (*) 位于有問題的列的下面。你可以添加 GROUP BY 子句來糾正幻燈片中的這個(gè)錯(cuò)誤。SELECTFROMdepartment_id, count(last_name)employeesGROUP BY department_id;在 SELECT 列表中的任何沒有使用子句中。函數(shù)的列或表達(dá)式必須放在 G

18、ROUP BY使用 Group 函數(shù)的查詢在 SELECT 列表中的任何列或表達(dá)式（非計(jì)算列）必須在 GROUP BY 子句中在 GROUP BY 子句中的列或表達(dá)式不必在 SELECT 列表中包含在 GROUP BY 子句中5-19SELECT department_id, COUNT(last_name)*ERRORine 1:ORA-00937: not a single-group group functionSELECT department_id, COUNT(last_name) FROMemployees;幻燈片20規(guī)定使用組函數(shù)查詢(續(xù))WHERE 子句不能用于限制組?；脽羝?/p>

19、中的 SELECT 子句結(jié)果出現(xiàn)錯(cuò)誤。因?yàn)槭褂?WHERE 子句限制了那些只有部門的平均薪水大于 $8,000 的分組才能顯示。你可以用 HAVING 約束組來糾正幻燈片中的錯(cuò)誤。 SELECT department_id, AVG(salary) FROM employeesHAVING AVG(salary) 8000GROUP BY department_id;使用 Group 函數(shù)的查詢不能使用 WHERE 子句來約束分組可以使用 HAVING 子句來約束分組在 WHERE 子句中不能使用組函數(shù)作為條件，只能用非計(jì)算列不能使用 WHERE 子句約束分組5-20SELECTdepartm

20、ent_id, AVG(salary) FROMemployeesWHEREAVG(salary) 8000GROUP BY department_id;WHERE AVG(salary) 8000*ERRORine 3:ORA-00934: group function is not allowed here幻燈片21約束分組結(jié)果用 WHERE子句約束選擇的行，用 HAVING 子句約束組。為了找到每個(gè)部門中的最高薪水，而且只顯示最高薪水大于 $10,000 的那些部門，你可以象下面這樣做：1.2.用部門號(hào)分組，在每個(gè)部門中找最大薪水。返回那些有最高薪水大于 $10,000 的雇員的部門。約

21、束分組結(jié)果EMPLOYEESTheumsalaryper department when it is greatern$10,0005-21幻燈片22HAVING 子句使用 HAVING 子句指定哪個(gè)組將被顯示，并且進(jìn)一步基于在語法中：信息約束分組。group_condition限制行分組，返回那些指定條件為 true 的組當(dāng)你使用 HAVING 子句時(shí)，Oracle 服務(wù)器執(zhí)行下面的步驟：1.2.3.行被分組。組函數(shù)被用于分組。匹配 HAVING 子句的標(biāo)準(zhǔn)的組被顯示。HAVING 子句可以優(yōu)先于為這樣更合邏輯。在 HAVING且組函數(shù)已被計(jì)算。GROUP BY 子句，但建議你先用 GROU

22、P BY 子句，因子句被用于 SELECT 列表的組之前，分組已形成，并教師注釋Oracle 服務(wù)器以下面的順序求子句的值：如果語句包含一個(gè) WHERE 子句，服務(wù)器建立候選行。服務(wù)器確定在 GROUP BY 子句中指定的分組。HAVING 子句進(jìn)一步約束結(jié)果那些在 HAVING 子句中不滿足分組標(biāo)準(zhǔn)的組。約束分組結(jié)果: HAVING 子句用 HAVING 子句約束分組：行被分組應(yīng)用組函數(shù)匹配 HAVING 子句的組被顯示5-22SELECTcolumn, group_functionFROMtableWHEREconditionGROUP BYgroup_by_expresHAVINGgro

23、up conditionORDER BYcolumn;幻燈片23HAVING 子句 (續(xù))幻燈片的例子顯示那些最高薪水大于 $10,000 的部門的部門號(hào)和最高薪水。你可以在 SELECT 列表中不使用組函數(shù)而使用 GROUP BY 子句。如果你基于組函數(shù)的結(jié)果約束行，你必須有一個(gè) GROUP BY 子句以及 HAVING子句。下面的例子顯示那些最高薪水大于 $10,000 的部門的部門號(hào)和平均薪水。SELECTFROMdepartment_id, AVG(salary)employeesGROUP BY department_idHAVINGmax(salary)10000;使用 HAVIN

24、G 子句5-23SELECTdepartment_id, MAX(salary) FROMemployeesGROUP BY department_idHAVINGMAX(salary)10000;幻燈片24HAVING 子句 (續(xù))幻燈片中的例子顯示那些合計(jì)薪水冊超過 $13,000 的每個(gè)工作崗位的 job ID 和合計(jì)薪水。該例子排除了銷售代表，并且用合計(jì)月薪排序列表。使用 HAVING 子句5-24SELECTjob_id, SUM(salary) PAYROLL FROMemployeesWHEREjob_id NOT LIKE %REP%GROUP BY job idORDER B

25、Y SUM(salary);HAVINGSUM(salary) 13000幻燈片25嵌套組函數(shù)組函數(shù)可以被嵌套兩層深度，幻燈片中的例子顯示最高平均薪水。嵌套組函數(shù)顯示最大平均薪水5-25SELECTFROMemployees GROUP BY department_id;MAX(AVG(salary)幻燈片26小結(jié)在SQL中有 7 個(gè)組函數(shù)可用：AVG 平均值COUNT 計(jì)數(shù)MAX 最大值 MIN 最小值 SUM 合計(jì) STDDEV 標(biāo)準(zhǔn)差 VARIANCE 方差你可以用 GROUP BY 子句創(chuàng)建子分組。分組可以用 HAVING 子句約束。在語句中將 HAVING 和 GROUP BY 子句

26、放在 WHERE 子句的后面。將 ORDER BY子句放在最后。Oracle 服務(wù)器按下面的順序求子句的值：1.2.3.如果語句包含一個(gè) WHERE 子句，服務(wù)器建立服務(wù)器確定在 GROUP BY 子句中指定的組。HAVING 子句進(jìn)一步約束不滿足在 HAVING 子句中分組標(biāo)準(zhǔn)的結(jié)果分組。小結(jié)在本課中, 您應(yīng)該已經(jīng)學(xué)會(huì)如何：使用組函數(shù) COUNT, MAX, MIN, AVG用 GROUP BY 子句寫查詢用 HAVING 子句寫查詢5-26SELECTcolumn, group_function(column)FROMtableWHEREconditionGROUP BYgroup_by_

27、expresHAVINGgroup_conditionORDER BYcolumn;幻燈片27練習(xí) 5概覽在該練習(xí)結(jié)束后，你應(yīng)該熟悉使用組函數(shù)和選擇數(shù)據(jù)分組?；诰砻鎲栴} 1-3，圈 True 或 False。注：列別名被用于問題中。教師注釋提示問題 #7：告訴學(xué)生思考關(guān)于 EMPLOYEES 表中的 MANAGER_ID時(shí)候決定經(jīng)理號(hào)，而不是工作崗位列。列，什么練習(xí) 5 概覽本章練習(xí)包括下面的：使用組函數(shù)寫查詢按行分組來得到多于一個(gè)結(jié)果用 HAVING 子句約束分組5-27練習(xí) 5決定下面三個(gè)語句的，圈 True 或 False。1.組函數(shù)在多行上計(jì)算，對每每個(gè)組產(chǎn)生一個(gè)結(jié)果。True/Fa

28、lseTrue2.組函數(shù)在計(jì)算中包含空值。True/FalseFalse. 組函數(shù)忽略空值。如果你想要包含空值，使用 NVL 函數(shù)。3.在分組計(jì)算中，WHERE 子句對行的限制在計(jì)算的前面。True/FalseTrue4.顯示所有雇員的最高、最低、合計(jì)和平均薪水，列分別為um、Minimum、Sum 和 Average。四舍五入結(jié)果為最近的整數(shù)，竟語句存在文本文件 lab5_4.sql 中。SELECT ROUND(MAX(salary),0) um,ROUND(MIN(salary),0) ROUND(SUM(salary),0) ROUND(AVG(salary),0)FROM emplo

29、yees;Minimum, Sum,Average5.修改 lab5_4.sql 中顯示每中工作類型的最低、最高、合計(jì)和平均薪水。再保存 lab5_4.sql 到 lab5_5.sql。運(yùn)行 lab5_5.sql 中的語句。SELECT job_id, ROUND(MAX(salary),0) ROUND(MIN(salary),0) Minimum, ROUND(SUM(salary),0) Sum, ROUND(AVG(salary),0) AverageFROM employeesGROUP BY job_id;um,6.寫一個(gè)查詢顯示每一工作崗位的人數(shù)。SELECTjob_id, CO

30、UNT(*)FROM employeesGROUP BY job_id;7.確定經(jīng)理人數(shù)，不需要列出他們，列MANAGER_ID 列決定經(jīng)理號(hào)。是 Number of Managers。提示：用SELECT COUNT(DISTINCT manager_id) Number of ManagersFROM employees;8.是 DIFFERENCE。寫一個(gè)查詢顯示最高和最低薪水之間的差。列SELECT MAX(salary) - MIN(salary) DIFFERENCEFROM employees;如果你后時(shí)間，完成下面的習(xí)題：9.顯示經(jīng)理號(hào)和經(jīng)理付給雇員的最低薪水。排除那些經(jīng)理未知的人。排除最低薪水小于等于 $6,000 的組。按薪水降序排序輸出。SELECT manager_id, MIN(salar