万恶之源-SQL

最新推荐文章于 2023-09-02 16:42:39 发布

Jrojyun

最新推荐文章于 2023-09-02 16:42:39 发布

阅读量86

点赞数

分类专栏： ORACLE sql

本文链接：https://blog.youkuaiyun.com/qq_33505270/article/details/115045878

版权

ORACLE 同时被 2 个专栏收录

25 篇文章

订阅专栏

sql

9 篇文章

订阅专栏

ORA-00904: "AVGCOMM": 标识符无效

ORA-00937: 不是单组分组函数

下面几个例子复现此报错

SCOTT@ prod>select deptno, avg(sal) avgcomm from emp group by deptno having avg(sal)>2000;

    DEPTNO    AVGCOMM
---------- ----------
        20       2175
        10 2916.66667

SCOTT@ prod>select deptno, avg(sal) avgcomm from emp where deptno=20 group by deptno having avg(sal)>2000;

    DEPTNO    AVGCOMM
---------- ----------
        20       2175

SCOTT@ prod>select deptno, avg(sal) avgcomm from emp where deptno=20 group by deptno having avgcomm>2000;
select deptno, avg(sal) avgcomm from emp where deptno=20 group by deptno having avgcomm>2000
                                                                                *
第 1 行出现错误:
ORA-00904: "AVGCOMM": 标识符无效


SCOTT@ prod>select deptno, avg(sal) avgcomm from emp where deptno=20  having avgcomm>2000;
select deptno, avg(sal) avgcomm from emp where deptno=20  having avgcomm>2000
                                                                 *
第 1 行出现错误:
ORA-00904: "AVGCOMM": 标识符无效


SCOTT@ prod>select deptno, avg(sal) avgcomm from emp where deptno=20 having avg(sal)>2000;
select deptno, avg(sal) avgcomm from emp where deptno=20 having avg(sal)>2000
       *
第 1 行出现错误:
ORA-00937: 不是单组分组函数


SCOTT@ prod>select deptno, avg(sal) avgcomm from emp where deptno=30 group by deptno having avg(sal)>2000;

未选定行

SCOTT@ prod>select deptno, avg(sal) avgcomm from emp  group by deptno having avg(sal)>2000;

    DEPTNO    AVGCOMM
---------- ----------
        20       2175
        10 2916.66667

SCOTT@ prod>

说一下where group by having 三个关键字 OCP考试有此题

1、3个关键字可以同时出现在一个SQL中

2、HAVING关键字后不允许使用列的别名aliases for the columns.，而且允许使用聚合函数aggregate functions.

3、WHERE 条件后允许使用别名aliases for the columns.

4、HAVING是为了排除一些结果

OWNER：Jrojyun

DATE:2021-03-21