Oracle DB 对行进行分组-group by、having
创建数据组所有组函数都将表当作一个大型的信息组。但是，有时需要将此信息表分成几个较小的组。可以通过使用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_functionFROM 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 13900AD_PRES 24000IT_PROG 28800AD_VP 34000ST_MAN 36400FI_ACCOUNT 39600ST_CLERK 55700SA_MAN 61000SH_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子句。
