oracle where與having

標籤：

where與having可以過濾，一般來說盡量使用where ，但是如果過濾條件中有組函數，只能使用having

 1 SQL> select deptno,avg(sal) 2   2    from emp 3   3    where deptno=10 4   4    group by deptno; 5   6 DEPTNO   AVG(SAL) 7 ------ ---------- 8     10       2950 9  10 SQL> 11 SQL> select deptno,avg(sal)12   2    from emp13   3    where avg(sal)>200014   4    group by deptno;15  16 select deptno,avg(sal)17   from emp18   where avg(sal)>200019   group by deptno20  21 ORA-00934: 此處不允許使用分組函數22  23 SQL> 24 SQL> select deptno,avg(sal)25   2    from emp26   3    group by deptno27   4    having avg(sal)>2000;28  29 DEPTNO   AVG(SAL)30 ------ ----------31     20 2258.3333332     10       2950

 

oracle where與having