神通数据库报错:SQL 错误 [40000]: ERROR, Attribute must be GROUPed or used in an aggregate function

最新推荐文章于 2024-06-20 07:30:00 发布

yunzhongyue23

最新推荐文章于 2024-06-20 07:30:00 发布

阅读量2k

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/qq_41707051/article/details/127867499

版权

在将原始SQL从MySQL迁移到另一个数据库系统时遇到了错误，原始SQL包含对字段IS_DELETED的平均值计算，但目标系统要求该字段必须用于GROUP BY或聚合函数中。通过修改SQL，将IS_DELETED加入到avg()函数中，问题得到解决，使得查询在新的神通数据库中能正常执行。

摘要由CSDN通过智能技术生成

原始sql语句,在mysql 正常执行

select
    round(avg(APPRAISETEACHER), 1) as APPRAISETEACHER,
    round(avg(APPRAISECOURSE), 1) as APPRAISECOURSE,
    round(avg(APPRAISEFORM), 1) as APPRAISEFORM,
    round(avg(APPRAISESATISFACTION), 1) as APPRAISESATISFACTION,
    IS_DELETED
from
    GBJYPX.zw_gbpx_appraise
where
    IS_DELETED = '0'

但是迁移到mysql 报错SQL 错误 [40000]: ERROR, Attribute ZW_GBPX_APPRAISE.IS_DELETED must be GROUPed or used in an aggregate function

修改之后在神通可以正常使用

select
    round(avg(APPRAISETEACHER), 1) as APPRAISETEACHER,
    round(avg(APPRAISECOURSE), 1) as APPRAISECOURSE,
    round(avg(APPRAISEFORM), 1) as APPRAISEFORM,
    round(avg(APPRAISESATISFACTION), 1) as APPRAISESATISFACTION,
    round(avg(IS_DELETED))
from
    GBJYPX.zw_gbpx_appraise
where
    IS_DELETED = '0'