Oracle命令 group by having
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
- 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 中不满足条件的信息,仅仅显示满足条件的信息。比如上例中只显示SAL>=2500的信息。
GROUP 的执行会消耗大量CPU资源,HAVING配合GROUP也算是一种优化吧。
更多Oracle相关信息见Oracle 专题页面 http://www.bkjia.com/topicnews.aspx?tid=12
评论暂时关闭