首页 > 代码库 > ORACLE SQL 组函数【weber出品必属精品】

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

2024-07-17 22:39:49   221人阅读

  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=75211 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      23752916.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. 聚合运算：sum6. having子句

     


联系
我们