欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

程序员文章站 2022-03-23 17:26:21
...

完整错误

今天更换数据库测试发现此问题,个人记录

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'a.add_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in class path resource [mapper/tisane/TisaneMapper.xml]
### The error may involve com.*.core.dao.customized.TisaneMapper.selectTstatisticByDay-Inline
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'a.add_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'a.add_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by] 

官方文档解释:

ONLY_FULL_GROUP_BY

拒绝选择列表,HAVING条件或ORDER BY列表引用非聚合列的查询,这些 列既不在GROUP BY子句中命名,也不在功能上依赖于(唯一确定的) GROUP BY列。

从MySQL 5.7.5开始,默认的SQL模式包括 ONLY_FULL_GROUP_BY。(在5.7.5之前,MySQL不检测功能依赖性,ONLY_FULL_GROUP_BY默认情况下不启用。有关5.7.5之前行为的描述,请参阅MySQL 5.6参考手册。)

标准SQL的MySQL扩展允许HAVING子句中的引用 到选择列表中的别名表达式。在MySQL 5.7.5之前,启用 ONLY_FULL_GROUP_BY 禁用此扩展,因此要求 HAVING使用非混淆表达式编写子句。从MySQL 5.7.5开始,这个限制被取消,因此HAVING无论是否ONLY_FULL_GROUP_BY启用,该子句都可以引用别名 。

解决方案

请参见 第12.19.3节“GROUP BY的MySQL处理”,里面有很详细的说明和实例。


相关标签: mysql sql