Oracle分组函数学习小结

一、Group by
   Group by子句（利用group by子句分组数据，当select语句中使用到组函数和字段一起连用时会用到group by，否则会出现错误）
   Group by把select查询的结果集分成几个小组，这个group by子句可以跟在where后面且在having前面。Group by子句也会触发排序操作，会按分组字段排序。
   格式：
   Select [组函数和分组的字段].....from 表名 group by[字段1]，[字段2]，。。。。。。；
   Oracle sql语句顺序：
   a. Select 显示字段或组函数 from 表名
   b. [ Where 过滤条件]  (不能使用组函数 ，不能使用列别名)
   c. [Group by 分组条件] （不能使用列别名，只可是字段名，不可是组函数）
   d. [Having 分组的过滤条件] (可以使用组函数)
   e. [ Order by 排序 ] （可以使用列别名，可以使用组函数）
   说明：其中的[ ]是可选项
   注意：group by分组不能用列的别名
   注意：只要写了group by子句，select后就只能用group by之后的字段或者是组的函数。Having子句可以过滤组函数结果或者分组的信息，并且写在group by子句后。
   使用group by子句时，必须满足下面的一些原则：
   a、在select子句的后面，只能有两种类型的表达式，一种是组函数，一种是出现在group by子句后面的列名。
   b、没有出现在group by子句后面的列名不能出现在select子句中非组函数表达式中，但是出现在group by子句后面的列名可以不出现在select子句中非组合函数表达式中。
   c、如果使用了where子句，那么所有参加分组计算的数据必须首先满足where子句指定的条件。
   d、在默认情况下，系统按照group by子句中指定的列升序排列，但是可以使用order by子句指定新的排列顺序。
   使用group by 常见的错误：
   a、如果在select语句中没有出现group by子句，那么不能在select子句中同时出现单个列名和组函数的混合现象。
   b、如果希望限制分组中的数据，那么可以使用having子句而不能使用where子句。即having中可以出现组函数而where不能出现组函数。
   注意1：在没有group by时，select后不能把普通字段和组函数同时使用
   注意2：where子句只能够过滤记录，放单行函数。Where后面跟随的条件是对所有数据的过滤的条件，在where子句中不能出现组函数。
   注意3：如果希望按照多个列分组，那么会在group by子句后面出现多个列名。这些列名称得顺序非常重要。因为不同的顺序有不同的结果。

例子1：
   查询求各个部门的最小，最高，工资之和
   select dept_id,min(salary),max(salary),sum(salary) from s_emp group  by dept_id；
例子2：
   查询求各个部门的最小，最高，工资之和，部门名称
   select e.dept_id,d.name,min(salary),max(salary),sum(salary) from s_emp e ,  s_dept d where e.dept_id=d.id group by e.dept_id,d.name;
例3：
   找出各个部门的平均工资
   select dept_id,avg(salary) from s_emp group by dept_id;
   注：在没有group by时，select后不能把普通字段和组函数同时使用
例4：
   求各个部门不同职位有多少人
   select dept_id,title,count(*) from s_emp group by dept_id,title;
例5：求除了42部门以外的各个部门的平均工资
   select dept_id,avg(salary) from s_emp  where  dept_id<>42 group by dept_id;
例7：求各个部门的平均工资
   select max(d.name),max(r.name),avg(salary) from s_emp e,s_dept d,s_region r where e.dept_id=d.id and d.region_id=r.id  group by dept_id;

 

二、Having
   Having子句(是对分组之后的数据进行过滤，所以使用having时必须用group by先分组)
   Having中的组函数可以不是select 中的组函数，利用having子句过滤分组的行。
   注意：having要先过滤掉不需要的记录，然后再进行分组操作，提高效率。having子句可以过滤组函数结果或是分组的信息，且写在group by子句后。
例：
   求平均工资大于2000的部门
   select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>2000;

 

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

2、组函数的类型：
   •AVG 平均值
   •COUNT 计数
   •MAX 最大值
   •MIN 最小值
   •STDDEV 标准差
   •SUM 合计
   •VARIANCE 方差
   a.所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。
   b.DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL ，因此不需要指定。
   c.用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
   d.当使用 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;

 

四、多于一个列的分组
   显示在每个部门中付给每个工作岗位的合计薪水的报告。（先按部门分组，再按部门下工作岗位分组）
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(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代码
   ELECT department_id, count(last_name)   
   FROM employees   
   GROUP BY department_id;  
   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 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 约束组来纠正幻灯片中的错误。

Java代码
   SELECT department_id, AVG(salary)   
   FROM employees;   
   HAVING AVG(salary) > 8000  
   GROUP BY department_id;  
   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);  
   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;