ORACLE命令 group by having

先通覽我們要查詢的表：

SQL> SELECT * FROM EMP ;      EMPNO ENAME      JOB         MGR HIREDATE          SAL DEPTNO---------- ---------- --------- ----- ----------- --------- ------      7369 SMITH      CLERK      7902 1980/12/17     800.00     20      7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00     30      7521 WARD       SALESMAN   7698 1981/2/22     1250.00     30      7566 JONES      MANAGER    7839 1981/4/2      2975.00     20      7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00     30      7698 BLAKE      MANAGER    7839 1981/5/1      2850.00     30      7782 CLARK      MANAGER    7839 1981/6/9      2450.00     10      7788 SCOTT      ANALYST    7566 1987/4/19     3000.00     20      7839 KING       PRESIDENT       1981/11/17    5000.00     10      7844 TURNER     SALESMAN   7698 1981/9/8      1500.00     30      7876 ADAMS      CLERK      7788 1987/5/23     1100.00     20      7900 JAMES      CLERK      7698 1981/12/3      950.00     30      7902 FORD       ANALYST    7566 1981/12/3     3000.00     20      7934 MILLER     CLERK      7782 1982/1/23     1300.00     10 14 rows selected

我們對JOB和DEPTNO進行統計匯總，並過濾掉SAL<2500的資訊。

SQL> SELECT JOB, DEPTNO, AVG(SAL)AS AVG_SAL FROM EMP GROUP BY JOB,DEPTNO  HAVING AVG(SAL) >=2500  ORDER BY JOB ASC; JOB       DEPTNO    AVG_SAL--------- ------ ----------ANALYST       20       3000MANAGER       20       2975MANAGER       30       2850PRESIDENT     10       5000

可以看出，HAVING 對於GROUP BY ,類似於WHERE 對於SELECT  。 

HAVING用於過濾掉GROUP BY 中不滿足條件的資訊，僅僅顯示滿足條件的資訊。比如上例中只顯示SAL>=2500的資訊。

GROUP 的執行會消耗大量CPU資源，HAVING配合GROUP也算是一種最佳化吧。