ORACLE SQL 組函數【weber出品必屬精品】

標籤：style   blog   color   使用   io   strong   資料   for   

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子句