oracle 双重分组,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 函数强制组函数包含空值

Java代码  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 子句中

Java代码  SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id ;SELECT 子句指定要返回的列：在 EMPLOYEES 表中的部门号你在 GROUP BY 子句中指定分组的所有薪水的平均值FROM 子句指定数据库必须访问的表：EMPLOYEES 表。WHERE 子句指定被返回的行。因为无 WHERE 子句默认情况下所有行被返回。GROUP BY 子句指定行怎样被分组。行用部门号分组，所以 AVG 函数被应用于薪水列，以计算每个部门的平均薪水。注意：分组结果被以分组列隐式排序，可以用 ORDER BY 指定不同的排序顺序，但只能用组函数或分组列。

Java代码  SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

ORDER BY AVG(salary);四。多于一个列的分组显示在每个部门中付给每个工作岗位的合计薪水的报告。(先按部门分组，再按部门下工作岗位分组)

Java代码  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 列表中

Java代码  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 子句来纠正幻灯片中的这个错误。

Java代码  SELECT department_id, count(last_name)

FROM employees

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

Java代码  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 hereWHERE 子句不能用于限制组。幻灯片中的 SELECT 子句结果出现错误。因为使用 WHERE 子句限制了那些只有部门的平均薪水大于 $8,000 的分组才能显示。你可以用 HAVING 约束组来纠正幻灯片中的错误。

Java代码  SELECT department_id, AVG(salary)

FROM employees;

HAVING AVG(salary) > 8000

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

Java代码  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);7.嵌套组函数求每个部门的最大平均薪水。

Java代码  select max(avg(salary)) from employees group by department_id;