創建數據組
所有組函數都將表當作一個大型的信息組。但是，有時需要將此信息表分成幾個較小的組。可以通過使用GROUP BY子句完成此任務。
創建數據組：GROUP BY子句的語法 可以通過使用GROUP BY子句將表中的行分成較小的組。 SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];可以使用GROUP BY子句將表中的行分成組。然後，可以使用組函數返回每個組的匯總信息。在該語法中： group_by_expression 指定某些列，這些列的值確定對行進行分組的基准准則 ? 除非在GROUP BY子句中指定了單個列，否則即使在SELECT子句中包括組函數，也不能選擇單個結果。如果未在GROUP BY子句中包括列的列表，則會收到一條錯誤消息。 ? 通過使用WHERE子句，可以在將行分成多個組之前先排除某些行。 ? 必須將列包括在GROUP BY子句中。 ? 不能在GROUP BY子句中使用列別名。
使用GROUP BY子句 SELECT列表中未出現在組函數中的所有列都必須包含在GROUP BY子句中。 hr@TEST0924> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;使用GROUP BY子句時，應確保將SELECT列表中未出現在組函數中的所有列都包含在GROUP BY子句中。示例顯示每個部門的部門編號和平均薪金。下面介紹含有 GROUP BY子句的SELECT語句是如何進行求值的： ? SELECT：子句指定要檢索的列，如下所示： - EMPLOYEES：表中的部門編號列 - GROUP BY：子句指定的組中所有薪金的平均值 ? FROM：子句指定數據庫必須訪問的表：EMPLOYEES表。 ? WHERE：子句指定要檢索的行。由於沒有WHERE子句，默認情況下會檢索所有行。 ? GROUP BY：子句指定如何對行進行分組。由於是按部門編號對行進行分組，因此應用於薪金列的AVG函數會計算每個部門的平均薪金。注：要按升序或降序對查詢結果進行排序，請在查詢中包含ORDER BY子句。
使用GROUP BY子句 GROUP BY列不一定要出現在SELECT列表中。 hr@TEST0924> SELECT AVG(salary) FROM employees GROUP BY department_id ;
AVG(SALARY) ----------- 8601.33333 4150 7000 9500 10000 19333.3333 10154 3475.55556 6500 8955.88235 4400 5760
12 rows selected.
按多個列進行分組 有時，需要查看組內的各個組的結果。 hr@TEST0924> SELECT department_id, job_id, sum(salary) FROM employees GROUP BY department_id, job_id ORDER BY job_id; DEPARTMENT_ID JOB_ID SUM(SALARY) ------------- ---------- ----------- 110 AC_ACCOUNT 8300 110 AC_MGR 12008 10 AD_ASST 4400 ... 20 rows selected.
此示例顯示一個報表，其中顯示要付給各個部門中每種職務的薪金總和。 EMPLOYEES表首先按部門編號進行分組，然後在各個組中又按職務進行分組。例如，將部門50 中的四個倉儲職員分成一個組，並為該組中的所有倉儲職員生成一個結果（薪金總和）。
通過列出多個GROUP BY列，可以返回組和子組的匯總結果。GROUP BY子句對行進行分組，但不保證結果集的順序。要對組進行排序，請使用ORDER BY子句。在示例中，包含GROUP BY子句的SELECT語句按如下方式進行求值： ? SELECT子句指定要檢索的列： - EMPLOYEES表中的部門ID - EMPLOYEES表中的職務ID - GROUP BY子句指定的組中所有薪金的總和 ? FROM子句指定數據庫必須訪問的表：EMPLOYEES表。 ? WHERE子句將結果集限定為部門ID 大於40 的行。 ? GROUP BY子句指定應如何對結果行進行分組： - 首先，按部門ID 對行進行分組 - 其次，在部門ID 組中按職務ID 對行進行分組 ? ORDER BY子句按部門ID 對結果進行排序。注：SUM函數將應用於每個部門ID 組的結果集中所有職務ID 的薪金列。另外，請注意，不返回SA_REP 行。此行的部門ID 為NULL，因此不滿足WHERE條件。
使用組函數的非法查詢 SELECT列表中不在聚集函數中的任何列或表達式都必須出現在GROUP BY子句中： hr@TEST0924> SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) FROM employees * ERROR at line 1: ORA-00937: not a single-group group function 必須添加GROUP BY子句，才能對每個department_id對應的姓氏進行計數。只要在同一個SELECT語句中混合使用單個項(DEPARTMENT_ID) 和組函數(COUNT)，就必須包括一個指定這些單個項（本例中為DEPARTMENT_ID）的GROUP BY子句。如果缺少GROUP BY子句，則會出現錯誤消息“not a single-group group function（不是一個組的組函數）”，而且顯示一個指向錯誤列的星號(*)。可通過添加GROUP BY子句更正第一個示例中的錯誤： hr@TEST0924> SELECT department_id, count(last_name) FROM employees GROUP BY department_id;
DEPARTMENT_ID COUNT(LAST_NAME) ------------- ---------------- 100 6 30 6 1 20 2 70 1 90 3 110 2 50 45 40 1 80 34 10 1 60 5
12 rows selected.
SELECT列表中不在聚集函數中的任何列或表達式都必須出現在GROUP BY子句中。
在第二個示例中，job_id既不在GROUP BY子句中也不在組函數中，因此將出現“not a GROUP BYexpression（不是GROUP BY表達式）”錯誤。可通過在GROUP BY子句中添加job_id更正第二個示例中的錯誤。 hr@TEST0924> SELECT department_id, job_id, COUNT(last_name) FROM employees GROUP BY department_id, job_id;
DEPARTMENT_ID JOB_ID COUNT(LAST_NAME) ------------- ---------- ---------------- 110 AC_ACCOUNT 1 90 AD_VP 2 50 ST_CLERK 20 ... 20 rows selected.
使用組函數的非法查詢 ? 不能使用WHERE子句限定組。 hr@TEST0924> SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id * ERROR at line 1: ORA-00934: group function is not allowed here
? 可以使用HAVING子句限定組。 ? 不能在WHERE子句中使用組函數。
不能使用WHERE子句限定組。示例中的SELECT語句產生了一個錯誤，因為該語句使用WHERE子句限定顯示平均薪金大於$8,000 的那些部門的平均薪金。但是，通過使用HAVING子句限定組，可以更正該示例中的錯誤： hr@TEST0924> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 8000;
DEPARTMENT_ID AVG(SALARY) ------------- ----------- 100 8601.33333 20 9500 70 10000 90 19333.3333 110 10154 80 8955.88235
6 rows selected.
限定組結果 與使用WHERE子句限定所選行的方式相同，可以使用HAVING子句限定組。要在最高薪金大於$10,000 的每個部門中查找最高薪金，需要執行以下操作： 1.通過按部門編號進行分組，查找每個部門的最高薪金。 2.將組限定為最高薪金大於$10,000 的部門。
使用HAVING子句限定組結果 使用HAVING子句時，Oracle Server 將按以下方式對組進行限定： 1. 對行進行分組。 2. 應用組函數。 3. 顯示符合HAVING子句的組。 SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
可使用HAVING子句指定要顯示的組，該子句基於匯總信息進一步限定組。在上述語法中，group_condition用於限定滿足指定條件的組的返回行組。使用HAVING子句時，Oracle Server 會執行以下步驟： 1.對行進行分組。 2.對組應用組函數。 3.顯示符合HAVING子句中的標准的組。 HAVING子句可放在GROUP BY子句之前，但建議將GROUP BY子句放在前面，因為這樣更符合邏輯。應先形成組並計算組函數，然後再對SELECT列表中的組應用HAVING子句。注：WHERE子句限定行，而HAVING子句限定組。
使用HAVING子句 hr@TEST0924> SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;
DEPARTMENT_ID MAX(SALARY) ------------- ----------- 100 12008 30 11000 20 13000 90 24000 110 12008 80 14000
6 rows selected.
示例顯示最高薪金大於$10,000 的部門的部門編號和最高薪金。可以在SELECT列表中使用GROUP BY子句，而不使用組函數。如果根據組函數的結果來限定行，則必須采用GROUP BY子句和HAVING子句。下面的示例顯示最高薪金大於$10,000 的部門的部門編號和平均薪金： hr@TEST0924> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING max(salary)>10000;
DEPARTMENT_ID AVG(SALARY) ------------- ----------- 100 8601.33333 30 4150 20 9500 90 19333.3333 110 10154 80 8955.88235
6 rows selected.
使用HAVING子句 hr@TEST0924> SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);
JOB_ID PAYROLL ---------- ---------- PU_CLERK 13900 AD_PRES 24000 IT_PROG 28800 AD_VP 34000 ST_MAN 36400 FI_ACCOUNT 39600 ST_CLERK 55700 SA_MAN 61000 SH_CLERK 64300
9 rows selected.
示例顯示工資合計超過$13,000 的每個職務的職務ID 和月薪總額。該示例將銷售代表排除在外，而且按月薪總額對列表進行排序。 嵌套組函數 下列語句顯示最高平均薪金： hr@TEST0924> SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;
MAX(AVG(SALARY)) ---------------- 19333.3333
組函數可以嵌套兩層。示例計算每個department_id對應的平均薪金，然後顯示最高平均薪金。請注意，嵌套組函數時，必須使用GROUP BY子句。
