ORACLE SQL 组函数【weber出品必属精品】

1. 组函数：对一组数据进行加工，每组数据返回一个值

2. 常用的组函数：count()  avg()  max()   min()  sum()  

3. count()函数 

   1. count(*) :返回总共的行数，不去除NULL值

   2. count(column)：返回非NULL行的数量

   SQL> select count(*) ,count(sal),count(comm) from emp;
    
     COUNT(*) COUNT(SAL) COUNT(COMM)
   ---------- ---------- -----------
       14        14          4

   3.除了count(*)意外，其他的组函数都要去除NULL

   SQL>select avg(sal),count(sal),sum(sal)/count(comm) average,count(sal)/count(nvl(comm,0)) average2,count(comm) countnotnull  from emp
    
   AVG(SAL) COUNT(SAL)  AVERAGE   AVERAGE2 COUNTNOTNULL
   ---------- ---------- ---------- ---------- ------------
   2073.21429     14    7256.25      1        4
    
   这里解释一下：avg()的除数是14，sum(sal)/count(comm）的除数则是4

4. DISTINCT()函数

   SQL> select empno,ename,comm from emp;
     
        EMPNO ENAME      COMM
   ---------- -------- ----------
         7369 SMITH
         7499 ALLEN       300
         7521 WARD        500
         7566 JONES
         7654 MARTIN     1400
         7698 BLAKE
         7782 CLARK
         7788 SCOTT
         7839 KING
         7844 TURNER        0
         7876 ADAMS
         7900 JAMES
         7902 FORD
         7934 MILLER
    
   SQL> select count(distinct comm) from emp;--返回唯一的非空comm的数量
      
   COUNT(DISTINCTCOMM)
   -------------------
                     4
    
   这里有个小问题：
   SQL> update emp set comm=300 where empno=7521
   1 row updated.
   SQL> select count(distinct comm ) from emp;
    
   COUNT(DISTINCTCOMM)
   -------------------
             3
   说明：distinct只是将重复的字段给过滤掉了。并不能说明：返回唯一的非空comm的数量，这种说法只能是在comm非空且不重复的情况下才可以。 

5.  GROUP BY 子句：创建分组数据
   在SELECT语句中，没有使用分组函数的列必须在GROUP By子句中

6. SQL> select deptno,avg(sal) from emp;
   select deptno,avg(sal) from emp
          *
   第 1 行出现错误:
   ​ORA-00937: 不是单组分组函数

   GROUP BY后面的列可以不出现在 SELECT语句中

   SQL> select avg(sal) from emp group by deptno;
    
     AVG(SAL)
   ----------
   1566.66667
         2375
   2916.66667

   多个列上使用 GROUP BY 子句

   SQL> select deptno,job,sum(sal) from emp group by deptno,job;
    
   DEPTNO JOB         SUM(SAL)
   ------ --------- ----------
       20 CLERK           1900
       30 SALESMAN        5600
       20 MANAGER         2975
       30 CLERK            950
       10 PRESIDENT       5000
       30 MANAGER         2850
       10 CLERK           1300
       10 MANAGER         2450
       20 ANALYST         7000
    
   先按照deptno进行分组，deptno相同的再按照job进行分组

     

7.   分组函数的误用
   

   1. 在SELECT 语句中，任何不在聚组函数中出现的列，必须在 GROUP BY 子句中

   2. 不能在 WHERE子句中对组函数做出限定，使用 HAVING 子句来限定分组

   SQL> select deptno,avg(sal) from emp where avg(sal)>2000  group by deptno;
   select deptno,avg(sal) from emp where avg(sal)>2000  group by deptno
                                         *
   第 1 行出现错误:
   ORA-00934: 此处不允许使用分组函数
   正确用法：
   SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
       DEPTNO   AVG(SAL)
   ---------- ----------
       20   2175
       10 2916.66667

8.  组函数的嵌套
   

   1. 组函数只能嵌套一层

   2. 使用组函数嵌套，必须跟group by子句

   错误的写法：
   SQL> select max(avg(sal)) from emp;
   select max(avg(sal)) from emp
              *
   ERROR at line 1:
   ORA-00978: nested group function without GROUP BY
    
   错误的写法：
   SQL> select deptno，max(avg(sal)) from emp group by deptno；
   select deptno, max(avg(sal)) from emp group by deptno
          *
   ERROR at line 1:
   ORA-00937: not a single-group group function
   因为select deptno查出来的是一列的内容，而组函数max()返回的只有一个值也错了、
   正确的写法：
   SQL> select max(avg(sal)) from emp group by deptno
    
   MAX(AVG(SAL))
   -------------
      2916.66667

9.   子句执行的顺序

   SELECT子句
   FROM 子句
   WHERE 子句
   GROUP BY 子句
   HAVING 子句
   
   select deptno,job,sum(sal) 
   from emp 
   where comm is not null 
   group by deptno,job 
   having sum(sal)>2000
   以上SQL语句的执行顺序
   
   1. from子句
   2. select子句
   3. where子句
   ​4. group by 子句
   5. 聚合运算：sum
   6. having子句