oracle group by 命令,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 3000

MANAGER 20 2975

MANAGER 30 2850

PRESIDENT 10 5000

可以看出，HAVING 对于GROUP BY ,类似于WHERE 对于SELECT  。

HAVING用于过滤掉GROUP BY 中不满足条件的信息，仅仅显示满足条件的信息。比如上例中只显示SAL>=2500的信息。

GROUP 的执行会消耗大量CPU资源，HAVING配合GROUP也算是一种优化吧。