Oracle之分组函数

组函数：
一、Oracle 服务器按下面的顺序求子句的值：
1. 如果语句包含一个 WHERE 子句，服务器建立侯选行。
2. 服务器确定在 GROUP BY 子句中指定的组。
3. HAVING 子句进一步约束不满足在 HAVING 子句中分组标准的结果分组。

二、组函数的类型：
•AVG 平均值•COUNT 计数•MAX 最大值•MIN 最小值•STDDEV 标准差•SUM 合计•VARIANCE 方差
1.所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。
2.DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL ，因此不

需要指定。
3.用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
4.当使用 GROUP BY 子句时，Oracle 服务器隐式以升序排序结果集。为了覆盖该默认顺序，DESC 可以被用

于 ORDER BY 子句。
使用类型：可以使用MIN 和MAX 用于任何数据类型，AVG、SUM、VARIANCE 和 STDDEV 函数只能被用于数字

数据类型。
COUNT 函数
COUNT 函数有三中格式：
COUNT(*)                 返回select语句的标准行，包括重复行，空值列的行
COUNT(expr)              由 expr 指定的非空值的数。
COUNT(DISTINCT expr)     返回在列中的由 expr 指定的唯一的非空值的数。 
在组函数中使用NVL 函数：
NVL 函数强制组函数包含空值

1. SELECT AVG(NVL(commission_pct, 0))FROM employees;  

SELECT AVG(NVL(commission_pct, 0))FROM employees;

三、group by子句
GROUP BY 子句把表中的行划分为组。然后你可以用组函数返回每一组的摘要信息。
原则
如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如

果你未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。
使用 WHERE 子句，你可以在划分行成组以前过滤行。
在 GROUP BY 子句中必须包含列。
在 GROUP BY 子句中你不能用列别名。
默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。
在SELECT 列表中的不在组函数中的所有列必须在GROUP BY 子句中

1. SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id ;  

SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id ;

SELECT 子句指定要返回的列：
在 EMPLOYEES 表中的部门号
你在 GROUP BY 子句中指定分组的所有薪水的平均值
FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
WHERE 子句指定被返回的行。因为无 WHERE 子句默认情况下所有行被返回。
GROUP BY 子句指定行怎样被分组。行用部门号分组，所以 AVG 函数被应用于薪水列，以计算每个部门的平

均薪水。
注意：分组结果被以分组列隐式排序，可以用 ORDER BY 指定不同的排序顺序，但只能用组函数或分组列。

1. SELECT department_id, AVG(salary)   
2. FROM employees   
3. GROUP BY department_id   
4. ORDER BY AVG(salary);  

SELECT department_id, AVG(salary)FROM employeesGROUP BY department_idORDER BY AVG(salary);

四。多于一个列的分组
显示在每个部门中付给每个工作岗位的合计薪水的报告。（先按部门分组，再按部门下工作岗位分组）

1. SELECT department_id dept_id, job_id, SUM(salary)FROM employeesGROUP BY department_id, job_id ;  

SELECT department_id dept_id, job_id, SUM(salary)FROM employeesGROUP BY department_id, job_id ;

SELECT 子句指定被返回的列：
部门号在 EMPLOYEES 表中
Job ID 在 EMPLOYEES 表中
你在 GROUP BY 子句中指定的组中所有薪水的合计
FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
GROUP BY 子句指定你怎样分组行：
首先，用部门号分组行。
第二，在部门号的分组中再用 job ID 分组行。
五.非法使用组函数：
1.在SELECT 列表中的任何列或表达式（非计算列）必须在GROUP BY 子句中，在GROUP BY 子句中的列或表

达式不必在SELECT 列表中

1. SELECT department_id, COUNT(last_name)FROM employees;   
2. SELECT department_id, COUNT(last_name)   
3.        *   
4. ERROR at line 1:ORA-00937: not a single-group group  

SELECT department_id, COUNT(last_name)FROM employees;SELECT department_id, COUNT(last_name)       *ERROR at line 1:ORA-00937: not a single-group group

无论何时，你在同一个 SELECT 语句中使用单独的列 (DEPARTMENT_ID) 和组函数 (COUNT) 的混合时，你必

须包括一个 GROUP BY 子句来指定单独的列 (在本例中，DEPARTMENT_ID)。如果缺少 GROUP BY，将会出现

错误信息 “not a single-group group function”，并且一个星号 (*) 位于有问题的列的下面。你可以

添加 GROUP BY 子句来纠正幻灯片中的这个错误。

1. SELECT department_id, count(last_name)   
2. FROM employees   
3. GROUP BY department_id;  

SELECT department_id, count(last_name)FROM employeesGROUP BY department_id;

2.非法使用Group 函数的查询
不能使用WHERE 子句来约束分组
可以使用HAVING 子句来约束分组
在WHERE 子句中不能使用组函数作为条件，只能用非计算列
例子：

1. SELECT department_id, AVG(salary)FROM employeesWHERE AVG(salary) > 8000GROUP BY department_id;   
2. WHERE AVG(salary) > 8000  
3.       *   
4. ERROR at line 3:ORA-00934: group function is not allowed here  

SELECT department_id, AVG(salary)FROM employeesWHERE AVG(salary) > 8000GROUP BY department_id;WHERE AVG(salary) > 8000      *ERROR at line 3:ORA-00934: group function is not allowed here

WHERE 子句不能用于限制组。幻灯片中的 SELECT 子句结果出现错误。因为使用 WHERE 子句限制了那些只

有部门的平均薪水大于 $8,000 的分组才能显示。
你可以用 HAVING 约束组来纠正幻灯片中的错误。

1. SELECT department_id, AVG(salary)   
2. FROM employees;   
3. HAVING AVG(salary) > 8000  
4. GROUP BY department_id;  

SELECT department_id, AVG(salary)FROM employees;HAVING AVG(salary) > 8000GROUP BY department_id;

6.约束分组结果
用HAVING 子句约束分组：
1.行被分组
2.应用组函数
3.匹配HAVING 子句的组被显示
例子：

1. SELECT job_id, SUM(salary) PAYROLL   
2. FROM employees   
3. WHERE job_id NOT LIKE '%REP%'  
4. GROUP BY job_id   
5. HAVING SUM(salary) > 13000  
6. ORDER BY SUM(salary);  

SELECT job_id, SUM(salary) PAYROLLFROM employeesWHERE job_id NOT LIKE '%REP%'GROUP BY job_idHAVING SUM(salary) > 13000ORDER BY SUM(salary);

7.嵌套组函数
求每个部门的最大平均薪水。

1. select max(avg(salary)) from employees group by department_id;