Oracle统计函数及分组查询

统计函数及分组查询
 1、统计函数
   在之前曾看过count()函数，此函数功能可以统计表中的数据量，实际上这个也是一个统计函数，且常用的统计 函数 有如下几个：
   .Count():查询表中的数据记录；
   .Avg():求出平均值；
   .Sum(): 求出；
   .Max():求出最大值；
   .Min():求出最小值；
范例：测试Count/avg/sum函数
  .统计出公司的所有雇员，每个月支付的平均工资和总工资.
   select count(empNo), sum(sal), avg(sal) from emp;
   select max(sal), min(sal) from emp;
注意点：关于Count()函数
   count函数的主要功能是进行数据的统计，但是在进行数据统计的时候，如果一张表中没有统计记录，count()也会返回结果，只是这个数据是“0”。
   select count(eName) from bonus;
如果使用的是其他函数，则有可能返回null，但是count永远都会返回一个具体的数字，这一点以后在开发之中都有用。
 2、分组统计
   在对分组之前必须明确一点，什么情况下可能分组，例如：.公司的所有雇员，要求男性一组，女性一组，之后可以统计男性和女性的数量；
      .按照年龄分组，18岁以上的分一组，18以下的分一组；
      .按照地区分组：北京一组，上海一组，四川一组；
这些信息如果都保存在了数据库中，肯定在数据的某一列上会存在重复的内容，例如：在性别分组的时候，性别肯定有重复，按照年龄分组(有一个范围重复),按照地区分组有一个地区的信息重复。
 所有分组之中有一个不成文的规定：当数据重复的时候分组才有意义，分组的关键字是Group by子句完成，此时sql语法如下：select [distinct] *|分组字段1[别名][，分组字段2[别名]，...]|统计函数
  from 表名称[别名] [,表名称[别名]，...]
  [where 条件(s)]
  [Group by 分组字段1[分组字段2,...]
  [Order by 排序字段 Asc|Desc [,排序字段 Asc|desc,..]
  范例：按照部门编号分组，求出每个部门的人数，平均工资。
  select deptNO count(empNo), avg(sal)
  from emp
  Group by deptNo;
但是现在一旦分组后，实际上对于语法上就会出现了新的限制，对于分组以下要求：
  .分组函数可以在没有分组的时候单独使用，可是却不能出现其他查询字段。
  select count(empNo) from emp;这样可以
  select empNo ,count(empNo) from emp 错误
还可以：select job, count(empNo), from emp
        group by job;正确（验证了不能出现其他查询字段）
  .分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能出现任何的其他字段。
  范例：按照职位分组，统计平均工资最高的工资
     select 这里不能出现其他字段的查询 max(avg(sal)) from emp group by job;
  .如果现在要分组的话，则select子句之后，只能出现分组的字段和统计函数，其他的字段不能出现；
    select job, count(empNo),avg(sal)
    from emp  group by job;
  范例：查询每个部门的名称、位置、部门的人数、平均工资
    select d.dName,e.empno, e.sal
    from dept d, emp e
    where d.deptNo=e.deptNo
    按照上述查询结果中，可以发现在dname字段上显示除了重复的数据，按照之前对分组的理解，只要数据重复了，那么就有可能进行分组的查询操作，但是此时与之前的分组不太一样，之前的分组是针对于一张实体表进行的分组(emp,dept都属于实体表),但是对于以上的数据是通过查询结果显示的，是一张临时的虚拟表，但是不管是否是实体表还是虚拟表，只要有重复，那么直接进行分组。
    select d.dname,count(e.empno),NVL(avg(e.sal), 0)
    from dept d, emp e
    where d.deptno= e.deptno(+)
    group by d.dname;
    之前的所有操作都是针对于单子段分组的，而实际上分组操作之中也可以是多字段分组。(多/单前提是要重复)
    范例：select d.deptno,d.dname, d.loc, count(e.empno), NVL(avg(e.sal), 0)
          from dept d, emp e
          where d.deptno=e.deptno(+)
          group by d.deptno, d.dname, d.loc;  
    范例：要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000。(之前唯一只有where限定查询的语法,且where子句不能使用统计函数)
   此时如果要对分组后的数据再次进行过滤，则要使用having子句完成，此时的SQL语法格式如下：
  select [distinct] *|分组字段1[别名][，分组字段2[   别名]，...]|统计函数
  from 表名称[别名] [,表名称[别名]，...]
  [where 条件(s)]
  [Group by 分组字段1[分组字段2,...]
  [Having分组后的过滤条件(可以使用统计函数)]
  [Order by 排序字段 Asc|Desc [,排序字段   Asc|desc,..]
   Select d.deptNO, d.dname,d.loc, count(e.empno) myCount, NVL(avg(e.sal)) mySal
   from dept d, emp e
   where d.deptno=e.deptno(+)
   Group by d.deptno, d.dname, d.loc
   Having avg(sal)>2000;
  总结：where和Having的区别：
  .where:是在执行Group by操作之前进行的过滤，表示从全部数据之中筛选出部分数据，在where中不能使用统计函数；
  .Having:是在group by分组之后的再次过滤，可以在Having子句中使用统计函数；
-------------------------------------------------子查询
 子查询=简单查询+限定查询+多表查询+ 统计查询的综合体，多表查询效率很好，针对小型的数据量是不错的选择，大数据量下，多表查询性能很差，但是最有利的子查询可以替代多表查询，实际上在开发中使用这种方式也很多。所谓子查询就是在一个查询之中嵌套了其他的查询，嵌套子查询之后的查询SQL语法如下：
select [distinct] *|分组字段1[别名][，分组字段2[     别名]，...]|统计函数 (
  select [distinct] *|分组字段1[别名][，分组字段2[   别名]，...]|统计函数
  from 表名称[别名] [,表名称[别名]，...]
  [where 条件(s)]
  [Group by 分组字段1[分组字段2,...]
  [Having分组后的过滤条件(可以使用统计函数)]
  [Order by 排序字段 Asc|Desc [,排序字段       Asc|desc,..] )

from 表名称[别名] [,表名称[别名]，...] (
  select [distinct] *|分组字段1[别名][，分组字段2[   别名]，...]|统计函数
  from 表名称[别名] [,表名称[别名]，...]
  [where 条件(s)]
  [Group by 分组字段1[分组字段2,...]
  [Having分组后的过滤条件(可以使用统计函数)]
  [Order by 排序字段 Asc|Desc [,排序字段             Asc|desc,..] )
[where 条件(s)] (
  select [distinct] *|分组字段1[别名][，分组字段2[   别名]，...]|统计函数
  from 表名称[别名] [,表名称[别名]，...]
  [where 条件(s)]
  [Group by 分组字段1[分组字段2,...]
  [Having分组后的过滤条件(可以使用统计函数)]
  [Order by 排序字段 Asc|Desc [,排序字段       Asc|desc,..])
[Group by 分组字段1[分组字段2,...]
[Having分组后的过滤条件(可以使用统计函数)]
[Order by 排序字段 Asc|Desc [,排序字段Asc|desc,..]
理论上子查询可以出现在查询语句中的任意位置上，但是从个人而言，子查询出现在where和from子句中较多：
   总结：.where:子查询一般只返回单行单列、多行单列  、单行多列的数据；
         .from:子查询返回的一般是多行多列的数据，  当作一张临时表出现。
   范例：select * from emp
  where (sal, job)=(
  select sal,job from emp
  where ename='ALLEN');返回单行多列的结果
如果现在的子查询返回的是多行单列数据的话，这个时候就需要使用三种判断符判断：IN、ANY、ALL；
1、IN操作符：用于指定一个子查询的判断范围
   这个操作符的使用实际上于前面的IN一样，唯一不同的是，里面的范围是由子查询指定。
   select *  from emp
 where sal IN (
    select sal
    from emp
     where job='MANAGER');
但是在使用IN的时候还要注意NOT IN问题，在子查询之中，如果一个内容是null，则不会查询出任何结果。
2、ANY操作符：与每一个内容相匹配，有三种匹配形式
   .=ANY: 功能与IN操作符是完全相同；
          select * from emp
   where sal= any( select sal
                 from emp
                 where job='MANAGER');
   .>ANY: 比子查询返回的记录最小的还要大的数据；
   select * from emp
   where sal>any( select sal
                 from emp
                 where job='MANAGER');
   .<ANY: 比子查询返回的记录最大的还要小的数据；
   select * from emp
   where sal<any( select sal
                 from emp
                 where job='MANAGER');

3、ALL操作符：与每一个内容相匹配，有两种形式：
   .>ALL:比子查询中返回的最大的记录还要大；
   select * from emp
   where sal<any( select sal
                 from emp
                 where job='MANAGER');
   .<ALL:比子查询中返回的最小的记录还要小；
   select * from emp
   where sal<any( select sal
                 from emp
                 where job='MANAGER');
以上的所有子查询都是Where子句中出现的，下面的是在from子句中出现的子查询，这个子查询一般返回的是多行多列的数据，当作一张临时表的方式来处理。
   范例：查询出每个部门的编号，名称，位置，部门人数，平均工资
    第一种方法：多表方法：
    select d.deptno, d.dname,d.loc, count(e.empno), avg(e.sal)
    from emp e, dept d
    where e.deptno(+)=d.deptno
    group by d.deptno,d.dname, d.loc;
    第二种方法：子查询法：通过子查询完成，所有的统计查询只能在Group by中出现，所以在子查询中负责统计数据，而在外部查询中，负责将统计数据和dept表数据相统一。
    select d.deptno ,d.dname,d.loc,temp.count,temp.avg
    from dept d,(
        select deptno dno, count(empno) count,  avg(sal) avg
        from emp
        group by deptno) temp
    where d.deptno= temp.dno(+);

    大部分情况下，如果最终的查询结果之中需要出现select子句，但是又不能直接使用统计函数的时候，就在子查询中统计信息，即：有复杂统计的地方都需要子查询。

转载:http://www.cnblogs.com/codc-5117/archive/2012/06/25/2562798.html