SQL基础认证

使用组功能

← 上一节 下一节 →

使用组函数报告聚合数据

SQL有许多预定义的聚合函数，可用于编写查询以准确生成此类信息.PROUP BY子句指定如何分组聚合信息时来自数据表的行，而HAVING子句筛选出不属于指定组的行.

聚合函数执行各种操作，例如计算表中的所有行，平均列的数据以及求和数值数据.聚合还可以搜索表以查找列中的最高"MAX"或最低"MIN"值.与其他类型的查询一样，您可以使用WHERE子句限制或过滤掉这些函数所处理的行.例如，如果经理需要知道组织中有多少员工，则可以使用名为COUNT(*)的聚合函数来生成此信息.下面的SELECT语句中显示的COUNT(*)函数计算所有行中的所有行. table.

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

结果表COUNT(*)函数是来自单行的单个列，称为标量结果或值.请注意，结果表的列标题与SELECT子句中指定的聚合函数的名称相对应.

一些常用的聚合函数如下所示 -

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

ALL和DISTINCT关键字是可选的，并执行与您学习编写的SELECT子句一样.ALL关键字是允许选项的默认值.语法中列出的表达式可以是常量，函数或列名，常量和列的任意组合.由算术运算符连接的函数.但是，聚合函数通常与列名一起使用.除COUNT函数外，所有聚合函数都不考虑NULL值.

使用聚合时，您必须了解并遵循两条规则:

  • 聚合函数可以在SELECT和HAVING子句中使用(本章后面将介绍HAVING子句).

  • 聚合函数不能在WHERE子句中使用.它违反将产生Oracle ORA-00934组功能，此处不允许出现错误信息.

插图

以下SELECT查询计算组织中的员工数量.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

以下SELECT查询返回员工工资的平均值组织.

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

以下SELECT query返回组织中员工工资的总和.

 SELECT SUM(Salary)total_sal 
 FROM employees; 
 TOTAL_SAL 
 --------- 
 87472

以下SELECT查询返回最旧和最新的查询雇用组织中员工的日期.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

GROUP BY

聚合函数通常与GROUP BY子句一起使用. GROUP BY子句使您可以使用聚合函数来回答更复杂的管理问题，例如:

每个部门的员工平均工资是多少？

每个部门有多少员工？

有多少员工正在处理特定项目？

按功能分组建立基于的数据组列和仅聚合组中的信息.分组标准由GROUP BY子句中指定的列定义.遵循此层次结构，首先在组中组织数据，然后WHERE子句限制每个组中的行.

使用GROUP BY子句的准则

(1)GROUP BY函数中使用的所有依赖列或列必须构成分组的基础，因此也必须包含在GROUP BY子句中.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2)GROUP BY子句不支持使用列别名，而是支持实际名称.

(3)GROUP BY子句只能与SUM等聚合函数一起使用AVG，COUNT，MAX和MIN.如果它与单行函数一起使用，Oracle会抛出异常为"ORA-00979:不是GROUP BY表达式".

(4)聚合函数不能在GROUP BY子句中使用. Oracle将返回"ORA-00934:不允许组功能"错误消息.

下面的查询列出了每个部门的员工数量.

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

同样，在查询下面查找总和每个部门各自工作ID的工资.请注意，该组是根据部门和工作ID建立的.因此它们出现在GROUP BY子句中.

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

以下查询也会产生相同的结果.请注意，分组基于部门ID和作业ID列，但不用于显示目的.

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

使用带有聚合函数的DISTINCT，ALL关键字

通过输入指定DISTINCT关键字参数，按功能分组仅考虑用于聚合的列的唯一值.通过使用输入参数指定ALL关键字，group by function会考虑用于聚合的列的所有值，包括空值和重复. ALL是默认规范.

HAVING子句

HAVING子句用于聚合函数，其方式与WHERE子句用于列名和表达式.实际上，HAVING和WHERE子句执行相同的操作，即根据条件从结果表中包含过滤行.虽然看起来HAVING子句可以过滤掉组，但它不会.Rather，HAVING子句过滤行.

当组的所有行都被删除时，组也是如此.总结，WHERE和HAVING子句之间的重要区别是:

WHERE子句用于在GROUPING操作之前(即，在计算聚合函数之前)过滤行.

HAVING子句在GROUPING操作之后(即，在计算聚合函数之后)过滤行.

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;


HAVING子句是一个与GROUP BY子句选项直接相关的条件选项，因为HAVING子句根据结果从结果表中删除行GROUP BY子句.

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function
← 上一节 下一节 →

相关菜鸟教程