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处理”,里面有很详细的说明和实例。
上一篇: xhtml和html的区别是什么
下一篇: 一个php的页面缓存类
推荐阅读
-
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 的问题 MySQL
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.
-
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
-
报错 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains解决
-
idea报错 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
-
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 的问题 MySQL
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
【问题解决】Expression #30 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zxj