Oracle DB 對行進行分組-group by、having__Oracle

建立資料群組 所有組函數都將表當作一個大型的資訊組。但是，有時需要將此 資訊表分成幾個較小的組。可以通過使用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 ;
DEPARTMENT_ID AVG(SALARY) ------------- -----------           100  8601.33333            30        4150                      7000            20        9500            70       10000            90  19333.3333           110       10154            50  3475.55556            40        6500            80  8955.88235            10        4400            60        5760
12 rows selected.
使用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.

GROUP BY列不一定要出現在SELECT子句中。例如，樣本中的SELECT語句顯示每個 部門的平均薪金，但沒有顯示相應的部門編號。但是如果沒有部門編號，結果看起來毫無 意義。 也可以在ORDER BY子句中使用組函數： hr@TEST0924> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary);
DEPARTMENT_ID AVG(SALARY) ------------- -----------            50  3475.55556            30        4150            10        4400            60        5760            40        6500                      7000           100  8601.33333            80  8955.88235            20        9500            70       10000           110       10154            90  19333.3333
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子句 hr@TEST0924> SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id > 40 GROUP BY department_id, job_id  ORDER BY department_id;
DEPARTMENT_ID JOB_ID     SUM(SALARY) ------------- ---------- -----------            50 SH_CLERK         64300            50 ST_CLERK         55700            50 ST_MAN           36400            60 IT_PROG          28800            70 PR_REP           10000            80 SA_MAN           61000            80 SA_REP          243500            90 AD_PRES          24000            90 AD_VP            34000           100 FI_ACCOUNT       39600           100 FI_MGR           12008           110 AC_ACCOUNT        8300           110 AC_MGR           12008
13 rows selected. 通過列出多個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對應的姓氏進行計數。
hr@TEST0924> SELECT department_id, job_id, COUNT(last_name) FROM employees GROUP BY department_id; SELECT department_id, job_id, COUNT(last_name) FROM employees GROUP BY department_id                       * ERROR at line 1: ORA-00979: not a GROUP BY expression
要麼在GROUP BY中添加job_id，要麼 從SELECT列表中刪除job_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] [O RDER 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 的部門的部門編號和平均薪金： <