[Err] 1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum

Dear 丶 2022-05-23 06:44 112阅读 0赞

今天将一个新项目发布到公司的测试环境上，被告知有一个模块报SQL语句的错误，立马在本地环境测试了一下，是正常的，想着奇怪了，然后再把出错的模块打断点截取测试环境出错的SQL语句。语句如下。

SELECT
    DATE_FORMAT(e.data_date, '%Y-%m') date,
    p.`name` PAIR_NAME,
    u.`name` UNIT_NAME,
    u.fgd_singlec_consumption_history fgd_history,
    c.`name` CORP_NAME,
    sum(e.consumption_sum) / sum(e.production_sum) SINGLEC_CONSUMPTION_CURRENT,
    sum(e.production_sum),
    sum(e.consumption_sum),
    sum(e.consumption_sum) - u.fgd_singlec_consumption_history * sum(e.production_sum) TOTAL_SAVING
    FROM
    ly_efficiency e
    LEFT OUTER JOIN sys_corp c ON c.pk_corp = e.pk_corp
    LEFT OUTER JOIN ly_unit u ON e.pk_unit = u.pk_unit
    LEFT OUTER JOIN ly_unit_pair p ON e.pk_unit_pair = p.pk_unit_pair
    WHERE
    IFNULL(e.jl_is_del, 0) = 0
    AND e.type = 101
    GROUP BY
    date,
    PAIR_NAME
    ORDER BY
    CORP_NAME,
    date

语句内容有点长，里面进行了各种sum函数运算之类的，然后放到本地环境数据库进行查询，是没有报错的，然后再放到测试环境的数据，结果报如下错误。

![70][]

\[Err\] 1055 - Expression \#3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'longyuan.u.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by。

上网查询了一下资料发现应该是数据库版本的问题，本地环境的数据库版本比较低，测试环境上的数据库版本比较高，在5.7.5以上，sql的默认模式配置是ONLY\_FULL\_GROUP\_BY，大概意思可能就是如果有用到sum之类的函数，要把字段都加入group by里吧。顺着这个思路把SQL语句改一改。

![70 1][]

这样就不会报错了。

**解决方法：**红框内为改动范围，这里我将所有用到sum函数的字段都加入group by聚合里，就可以解决了。

[70]: /images/20220523/ef21b5d0e3a74cd98d65c0d6c5de3eda.png
[70 1]: /images/20220523/cd04ea1b0d874e56ac131df49a01b533.png

[Err] 1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated

相关 MySQL报错1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colu

相关 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c

相关 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated

相关 MySQL[Err] 1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated

相关 [Err] 1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum

相关 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c

相关 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c

相关 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate