Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated ...

已于 2022-09-19 12:02:26 修改
阅读量5.1k 收藏 10
点赞数 2
文章标签： list hibernate java
于 2022-09-19 11:25:09 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_59025975/article/details/126930246
版权

在Mysql版本为5.7.25时，在使用使用group by 时，会出现Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated...错误,这个错误的原因是 group by后面需要加上，select中的所有字段。不然就会报这个错误。

1、查询时，出现如下错误：

org.hibernate.exception.SQLGrammarException: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'itsm.org.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:83)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
	at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
	at com.sun.proxy.$Proxy220.executeQuery(Unknown Source)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:703)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:199)
	at org.jeecgframework.minidao.aop.MiniDaoHandler.getReturnMinidaoResult(MiniDaoHandler.java:317)
	at org.jeecgframework.minidao.aop.MiniDaoHandler.invoke(MiniDaoHandler.java:102)
	at com.sun.proxy.$Proxy182.pagingQueryUserEventEntity(Unknown Source)
	at com.iservicecom.itsm.event.service.impl.DepartServiceRoleUserServiceImpl.pagingQueryUserEventEntity(DepartServiceRoleUserServiceImpl.java:187)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	at com.sun.proxy.$Proxy183.pagingQueryUserEventEntity(Unknown Source)
	at com.iservicecom.itsm.event.controller.UserEventController.technicianDatagrid(UserEventController.java:651)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:747)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:676)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.jeecgframework.core.aop.GZipFilter.doFilter(GZipFilter.java:152)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:140)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.jeecgframework.core.filter.XssFilter.doFilter(XssFilter.java:55)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'itsm.org.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2708)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2705)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:99)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:211)
	at sun.reflect.GeneratedMethodAccessor109.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
	... 82 more

2、解决

这个是由于 sql_mode 设置不当引起的，修改下 sql_mode 即可：

（1）第一种解决方法

①、查询 ：

select version(), @@sql_mode;

 ②、修改 sql_mode

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

查询查看：

（2）第二种解决方法：

推荐使用第二种，第一种方法新的连接生效，已有的连接重启服务将不会生效

①、配置my.cnf或my.ini,增加如下：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

②、重启mysql服务查看是否生效

关闭mysql服务：net stop mysql
启动mysql服务：net start mysql

 现在就可以了！

@十九.
关注
  • 2
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
小白求学进阶
06-11 7461
一、原因 聚合查询出现这样的报错是因为mysql设定的mode含有 ONLY_FULL_GROUP_BY mysql> select @@sql_mode; +------------------------------------------------------------------------------------------------------------...
[Err] 1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated colum
hhczy1003的专栏
08-23 1万+
这个错误发生在mysql 5.7.5 和以后上，因为5.7.5默认的sql模式配置是 ONLY_FULL_GROUP_BY，  这个配置启用的是 “严格ANSIsql 规则”，严格ANSI sql 规则要求在group by的时候，没有聚合的列，在group by的时候，必须全部包含在group by 的字段中。 没有聚合的列，指的是没有使用 max, min, count,
Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated...
working_hewei的博客
11-26 2201
记一个MySql的小坑，在Mysql版本为5.7.25时，在使用使用group by 时，会出现Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated...错误,这个错误的原因是 group by后面需要加上，select中的所有字段。不然就会报这个错误。 解决这个错误方法是更改Linu...
MYSQL:Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column
DXIANGH的博客
06-10 1711
mysql only_full_group_by问题
MySQL：SELECT list is not in GROUP BY clause 报错 解决方案
最新发布
换条路吧，编程没意思的~
06-20 761
MySQL 报错：SELECT list is not in GROUP BY clause and contains nonaggregated column 解决方案
MySQL报错“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”解决方法
热门推荐
lucky多多的博客
03-22 5万+
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
解决MYSQL查询报错 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column
谷咕咕
10-26 737
原因：在MySQL5.7.5后，默认开启了ONLY_FULL_GROUP_BY，所以导致了之前的一些SQL无法正常执行，其实，是我们的SQL不规范造成的，因为group by 之后，返回的一些数据是不确定的，所以才会出现这个错误。这个是一次性的，也可以在my.ini里面设置。
MYSQL查询报错 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column，
陌子曦的专栏
05-07 3560
完整报错如下：Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sa.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with s...
MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona…
12-14
在MySQL中，错误提示 "[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column…” 指的是在执行包含`GROUP BY`子句的查询时，你试图对未在`GROUP BY`子句中...
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
12-15
如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。(在5.7.5之前，MySQL没有检测...
mysql遇到Expression.docx
03-05
在MySQL中，当你尝试执行一个包含`GROUP BY`子句的查询时，可能会遇到"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column"这样的错误。这个错误提示表明你的查询违反了`...
MySQL使用中遇到的问题记录
12-15
当MySQL执行GROUP BY查询时，如果SELECT列表中包含未在GROUP BY中指定的非聚合列，且该列未包含在聚合函数（如SUM、AVG等）中，就会报错。解决这个问题的方法是调整`sql_mode`的设置。你可以通过登录MySQL并运行以下...
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
和代码死磕到底的博客
04-19 1万+
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_F...
【解决】 SELECT list is not in GROUP BY clause and contains nonaggregated column
丑小鸭
01-18 1万+
出错情况 问题分析 出现此异常的原因是：如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。 使用数据库的版本信息 mysql-8.0.15-winx64 解决策略 法 1 1 查看 sql_mode 1 使用以下命令查看自己的 sql_mode select @@global.sql_mode; 或者 show variables
【Error】Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is
邪神大人
01-07 433
由于使用Group by字段，及select 后的字段较多，导致此错误 1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by 解决方法： Linux:  ...
mysql group by 报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated c
wuxiaopengnihao1的博客
09-05 4039
这是MySQL8.0默认开启only_full_group_by模式导致的，该模式限定select后面跟的列，在group by后面必须要有。好了， 以上是本文所有内容，希望对大家有所帮助，也希望大家对。多多支持，你们的支持是我创作的动力！：该方式重启mysql会失效。修改配置文件my.cnf。重启mysql即可。
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
A乐神的博客
12-15 6619
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
MySQL遇见Expression this is incompatible with sql_mode=only_full_group_by
流风雨情的博客
11-20 229
当在查这个sql的时候 SELECT a.*, count(b.id) FROM stu a LEFT JOIN stu b ON a.class = b.class AND a.score < b.score GROUP BY a.id Expression #2 of SELECT list is not in GROUP BY clause and contains nona...
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
07-29
引用\[1\]、\[2\]和\[3\]都是关于MySQL报错的信息，其中提到了"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column"的错误。这个错误是由于在使用GROUP BY子句进行分组查询时，SELECT列表中的某些列没有在GROUP BY子句中出现，并且这些列也没有被聚合函数包含，导致不兼容的错误。这个错误通常是由于MySQL的sql_mode设置为only_full_group_by引起的。解决这个错误的方法是要么将SELECT列表中的列添加到GROUP BY子句中，要么将这些列用聚合函数进行处理，以确保查询的正确性。 #### 引用[.reference_title] - *1* *3* [MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ...](https://blog.csdn.net/Saintmm/article/details/124491860)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v4^insert_chatgpt"}} ] [.reference_item] - *2* [Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre](https://blog.csdn.net/weixin_44051060/article/details/105602832)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v4^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值