Oracle DB 对行进行分组-group by having

怼烎@ 2022-03-17 09:28 149阅读 0赞

分享一下我老师大神的人工智能教程。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

*  **创建数据组**

所有组函数都将表当作一个大型的信息组。但是，有时需要将此 信息表分成几个较小的组。可以通过使用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 的部门的部门编号和平均薪金：

*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.*