Oracle PLSQL之HAVING后面的条件可由聚合函数构成，也可由GROUP BY后的字段构成

最新推荐文章于 2024-08-13 16:09:20 发布

t0nsha

最新推荐文章于 2024-08-13 16:09:20 发布

阅读量7.3k

点赞数

分类专栏： Oracle - PL/SQL 文章标签： oracle sql

本文链接：https://blog.csdn.net/t0nsha/article/details/6910785

版权

Oracle - PL/SQL 专栏收录该内容

58 篇文章 0 订阅

订阅专栏

HAVING主要用于对分组后的数据进行过滤，

一般我们在其后接聚合函数（SUM，COUNT等），

除此之外我们还可以用GROUP BY后面的字段组成HAVING后的过滤条件。

SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 name1          loc1
    51 name1          name1
    52 loc1           loc1
 
7 rows selected
 

SQL> SELECT loc FROM dept GROUP BY loc HAVING count(deptno)>1;
 
LOC
-------------
loc1
 
SQL> SELECT loc FROM dept GROUP BY loc HAVING deptno>30;
 
SELECT loc FROM dept GROUP BY loc HAVING deptno>30
 
ORA-00979: not a GROUP BY expression
 
SQL> SELECT loc FROM dept GROUP BY loc,deptno HAVING deptno>30;
 
LOC
-------------
BOSTON
loc1
loc1
name1
 
SQL> SELECT loc,deptno FROM dept GROUP BY loc,deptno HAVING deptno>30;
 
LOC           DEPTNO
------------- ------
BOSTON            40
loc1              50
loc1              52
name1             51
 
SQL>