Oracle数据库基础10-组函数

本章介绍的组函数，是对多行的数据集合进行操作，对每组给出一个结果。如果没有对数据集合进行分组的话，则是把整个数据当成一组，给出一个结果。

1.组函数引入

我们使用<ANY子查询能够将子查询结果限制在小于最大值，所以我们可以用组函数MAX

即，<MAX

select * from emp where deptno =20 and sal <(select max(sal) from emp where deptno = 30);

结果：

2.组函数概述

• 组函数是对多行函数进行操作，操作时有如下注意事项：
• 组函数忽略空值。为了用一个值代替空值，用NVL、NVL2或COALESCE函数。
• 组函数默认考虑重复值。为了让组函数不考虑重复值，可以使用DISTICT关键字。
• 用于函数的参数类型可以是CHAR、CHAR2、NUMBER或DATE。

常用组函数

函数	说明
AVG(input)	求平均值，忽略空值且考虑重复值（以下同）
SUM(input)	求和
MAX(input)	求最大值
MIN(input)	求最小值
COUNT(*|input)	求行数（用*则包括空值的行）
STDDEV(input)	求标准差
VARIANCE(input)	求方差

例如：

select count(ename),max(sal),min(sal) from emp;

结果：

需要注意的是，MAX和MIN函数不仅可以使用在数字类型上，还可以用于任意数据类型。而AVG、SUM以及STDDEV和VARIANCE仅能用在数字类型上。

接下来介绍COUNT函数，COUNT函数的参数除了可以是字段值和表达式外，还可以是“*”。如果是字段值或表达式，则和其他组函数一样，忽略空值且考虑重复值；如果是“*”。则计算所有行，也包括空值。

例如：

select count(*),max(hiredate),min(hiredate) from emp;

结果：

3.创建分组

直到现在，所有的组函数都是针对整个数据集进行的操作。接下来，会将整个数据集分成若干个分组，针对每个分组使用组函数进行操作。

用 GROUP BY 子句可以把表中的数据集合划分为若干个组。GROUP BY子句后面的表达式用于指定分组的依据。GROUP BY子句置于WHERE子句后，ORDERBY子句前，后面的表达式必须包含字段，且不能使用列的别名。

在使用GROUP BY进行分组时，必须注意，如果SELECT子句中包含了组函数，就不能选择单独的字段，除非这个字段出现在GROUP BY子句后面。

例如：

select max(sal) from emp group by deptno;

结果：

注意，在SELECT子句中，只可以有组函数和分组字段（包含对分组字段的操作），如果包括其他字段报错。另外，如果GROUP BY子句后要跟有ORDER BY子句，则ORDER BY子句用于排序的字段必须是分组字段或组函数。

4.分组中再分组

同样的，SELECT子句和ORDER BY子句中只能包括分组字段和组函数。

例如：

select max(sal) from emp group by deptno,job;

结果：

5.约束分组结果

例如：

select max(sal) from emp where sal>2000 group by deptno,job;

结果：