oracle学习笔记之五 组函数

 1.Oracle包含以下组函数
  -AVG([DISTINCT|ALL]n) 返回平均值，忽略空值
  -COUNT({*|[DISTINCT|ALL]}expr) 返回记录的数量，用*包含空值，否则不包含空值
  -MAX([DISTINCT|ALL]expr)  返回最大值，忽略空值
  -MIN([DISTINCT|ALL]expr) 返回最小值，忽略空值
  -SUM([DISTINCT|ALL]n)  返回总值，忽略空值
  -STDDEV([DISTINCT|ALL]x)  返回标准差，忽略空值
  -VARIANCE([DISTINCT|ALL]x)  返回统计方差，忽略空值

 

2.AVG和SUM的用法
  -可以对数值型数据使用AVG和SUM 
  例：
     SELECT AVG(sal),SUM(sal)
     FROM   EMP
     WHERE  job LIKE 'SALES%';

 

3.MIN和MAX的用法
  -MIN和MAX可以用于任何数据类型
  例：
     SELECT MIN(hiredate),MAX(hiredate)
     FROM   emp;
  结果：
       MIN(HIREDATE)   MAX(HIREDATE) 
        17-12月-80      23-5月 -87 

 

4.COUNT的用法
1)COUNT(*)返回表中行的总数
  例：
     SELECT  COUNT(*)
     FROM    emp
     WHERE   deptno=30;
  结果：
        COUNT(*) 
              6 
2)COUNT(expr)返回非空行的数量
  例:
     SELECT  COUNT(comm)
     FROM    emp
     WHERE   deptno=30;
  结果：
       COUNT(*) 
              4

 

5.组函数和空值
1)组函数会忽略列中的空值
  例：
     SELECT  AVG(comm)
     FROM    emp;
  结果：
       AVG(COMM)
             550
2)NVL函数可以使组函数强制包含含有空值的记录
  例：
     SELECT AVG(NVL(comm,0))
     FROM   emp;
  结果：
       AVG(NVL(COMM,0)) 
             157.142857 

 

6.创建数据组
  SELECT     column,group_function(column)
  FROM       table
  [WHERE     condition]
  [GROUP BY  group_by_expression]
  [OEDER BY  column];
 
  -通过GROUP BY子句将表中的记录划分成若干个小组
  -GROUP BY子句中必须包含指定的列
  -GROUP BY子句中不能使用列的别名
  -当使用GROUP BY子句时Orale服务器会自动对结果集合默认按GROUP BY子句所指定的列升序排列
  -在SELECT列表中除了组函数外，所有列都必须包含在GROUP BY子句中
  例：
     SELECT   deptno,AVG(sal)
     FROM     emp
     GROUP BY deptno;
  结果：
        DEPTNO     AVG(SAL) 
          10       2916.66667 
          20       2175 
          30       1566.66667
   -GROUP BY所指定的列并不是必须出现在SELECT列表中
   例：
      SELECT   AVG(sal)
      FROM     emp
      GROUP BY deptno;
   结果：
             AVG(SAL) 
           2916.66667 
                 2175 
           1566.66667

 

7.按多个列分组
  例：
     SELECT   deptno,job,sum(sal)
     FROM     emp
     GROUP BY deptno,job;
  结果：
        DEPTNO     JOB       SUM(SAL) 
            10     CLERK         1300 
            10     MANAGER       2450 
            10     PRESIDENT     5000 
            20     CLERK         1900 
            20     ANALYST       6000 
            20     MANAGER       2975 
            30     CLERK          950 
            30     MANAGER       2850 
            30     SALESMAN      5600 

 

8.使用组函数的非法查询
  例：
     SELECT dept,COUNT(ename)
     FROM   emp;
  结果：
        ERROR 位于第 1 行:
        ORA-00937: 非单组分组函数
  纠正：
       SELECT   dept,COUNT(ename)
       FROM     emp
       GROUP BY deptno;
  结果：
        DEPTNO    COUNT(ENAME) 
            10               3 
            20               5 
            30               6 

9.限制组结果
  使用HAVING子句限制组
  -对记录分组
  -在分组的基础上应用组函数
  -与HAVING子句匹配的结果才输出

  SELECT    column,group_function
  FROM      table
  [WHERE    condition]
  [GROUP BY group_by_expression]
  [HAVING   group_condition]
  [ORDER BY column];
 
  例1：
     SELECT   deptno,max(sal)
     FROM     emp
     WHERE    max(sal)>2900
     GROUP BY deptno;
  结果：
        ERROR 位于第 3 行:
        ORA-00934: 此处不允许使用分组函数
  纠正：
        SELECT   deptno,max(sal)
        FROM     emp
        GROUP BY deptno
        HAVING   max(sal)>2900;
  结果：
        DEPTNO     MAX(SAL) 
            10         5000 
            20         3000
  例2：
        SELECT   job,SUM(sal) PAYROLL
        FROM     emp
        WHERE    job NOT LIKE 'SALES%'
        GROUP BY job
        HAVING   SUM(sal)>5000
        ORDER BY SUM(sal);
  结果：
        JOB           PAYROLL 
        ANALYST          6000 
        MANAGER          8275 

 

10.组函数嵌套
   -与单行函数不同，组函数只能嵌套两层
   例：显示平均薪水的最大值
       SELECT   max(avg(sal))
       FROM     emp
       GROUP BY deptno;
   结果：
         MAX(AVG(SAL)) 
            2916.66667 

练习
1.使用emp表显示所有雇员的最多、最少、总和、平均薪水
  
   SELECT ename,MAX(sal),MIN(sal),SUM(sal),AVG(sal)
   FROM   emp;
 
2.显示emp表中不同部门编号的数量
 
   SELECT COUNT(DISTINCT deptno)
   FROM   emp;

3.在emp表中根据job列分组显示雇员的最多、最少、总和、平均薪水
  
   SELECT   job,MAX(sal),MIN(sal),SUM(sal),AVG(sal)
   FROM     emp
   GROUP BY job;

4.使用emp表显示job名、每组最多、最少、总和、平均薪水
  要求：按job列分组，ename列的名字不是以A开头，且任何组的最少薪水大于1600

   SELECT   job,MAX(sal),MIN(sal),SUM(sal),AVG(sal)
   FROM     emp
   WHERE    ename NOT LIKE 'A%'
   GROUP BY job
   HAVING   MIN(sal)>1600

5.显示部门名和每个部门的累计薪水，要求每个部门的累计薪水大于3000

   SELECT   dname,SUM(sal)
   FROM     emp e,dept d
   WHERE    e.deptno=d.deptno
   GROUP BY dname
   HAVING   SUM(sal)>3000;

6.显示每个部门、每个岗位的最高及最低薪水

   SELECT   deptno,job,MAX(sal),MIN(sal)
   FROM     emp
   GROUP BY deptno,job;