Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column……报错的解决办法

最新推荐文章于 2025-03-06 21:09:40 发布
最新推荐文章于 2025-03-06 21:09:40 发布
阅读量2.1k 收藏 4
点赞数 1
分类专栏： 问题汇总

大概是第二次遇到这个问题才想起来写这个博客记录…
这个报错是执行有GROUP BY的语句时出现的,原因是MySQL启用了默认的only_full_group_by SQL模式,导致GROUP BY语句报错.
执行select @@sql_mode可以看到sql_mode的值为
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
所以要将ONLY_FULL_GROUP_BY去掉,执行如下两句即可

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

再次使用GROUP BY查询,问题解决
只是留个记录…避免日后再遇到

参考博客:
https://blog.youkuaiyun.com/fansili/article/details/78664267
https://blog.youkuaiyun.com/renyiforever/article/details/80571884

mysql报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
DN金猿的博客
06-28 988
经过查询资料发现group by 聚合操作的时候，进行查询操作的列没有在group by 中出现，那么这个sql就是不合法的。主要原因就是设置的与sql_mode=only_full_group_by不兼容。今天在mysql5.7.x 升级到8.0.x版本的时候 项目接口报错。通过查询发现mysql5.7.5及以上的版本都会依赖检测功能,也就是默认会开启only_full_group_by。8.0.x的版本不支持NO_AUTO_CREATE_USER，去掉即可。
出现User 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated 解决方法
码农研究僧的博客
04-24 2811
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'easy-admin.students.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
MySQL报错1055- Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum
Ariel_欢
05-15 6787
1.问题描述： 今天在写SQL语句时，需要将数据先按用户分组汇总，再按物品类型（测试数据库中字段是item_name）进行分组汇总，SQL语句中使用了group by分组函数，然后执行SQL报错： 执行的SQL语句： SELECT a.user_id,a.item_name,a.item_id,a.approver_id,a.create_time,a.supposed_return_time FROM `t_approval` as a WHERE now() > a.supposed
mysql5.7以上报错：Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum
hhq850741642的博客
09-29 854
在写sql时报错信息：1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘pms.ru.role_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byselect @@glo
MySQL 报错: “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column“
最新发布
总结复盘，才能不迷失自己！
03-06 506
1.问题描述MySQL 中出现 “1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column” 错误？
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘c.countFan
weixin_43802097的博客
05-12 1335
mysql中的sql__@@mode问题， 导致sql无法运行 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'c.countFangWenCiShuByToday' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only
MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic
weixin_63716356的博客
07-24 2062
MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic字面翻译： SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“grades.order_id” 它在功能上不依赖于 GROUP BY 子句中的列； 这与 sql_mode=only_full_group_by 不兼容使用GROUP BY 语句违背了 sql_mode=only_
【MySQL】报错1055- Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated
weixin_61428498的博客
08-01 534
1055- Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum完美解决！！！！！
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column
天不拘兮地不羁
07-08 5432
“Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘workflow.s.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with s...
navicat15执行查询出现错误:Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated
ahmcwt的专栏
11-24 823
SELECT c.cust_id,c.cust_name,o.order_num,o.order_date,o.cust_id,count(*) from customers c LEFT JOIN orders o on c.cust_id=o.cust_id GROUP BY c.cust_id; 本机:windows10,navicat15,mysql8.0.22 在执行上述语句查询时,因为加了另一个表的字段:o.order_num,o.order_date,o.cust_id,运行就报错,
运行报错Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column xx....
爱码士的博客
02-01 1672
安装了mysql5.7，用group by 查询时抛出如下异常： Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘userinfo.t_long.user_name’ which is not functionally dependent on columns in ...
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
二十一克阳光！的博客
12-05 1033
在mysql中使用 group by 出现的问题 ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘ynn.goods.goods_id’ which is not functionally dependent on column...
MySQL报错1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colu
热门推荐
JaneYork的博客
02-17 1万+
1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fbjs.mscc.ContactTime' which is not functionally dependent on columns in GROUP BY clause; this is incom...
Mysql1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated问题报错
weixin_43822858的博客
03-05 322
今天写sql分组查询的时候报了这么个错，网上查解决办法都是改 @@global.sql_mode。如： 但是不知道什么原因(可能是因为我下载的是免安装版？)，我改了之后group分组还是报上面的错误。 都准备放弃了，准备接着往后写，突然发现了问题所在==||； 先说解决方法吧(其实也不算解决方法吧)，免得大家着急：) 在你按某字段分组的时候，其他字段每一列的值可能不同，比如下图所示的表： 此时运行sql语句： SELECT id from test GROUP BY area; 就会报标题所示错误。
mysql:Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column
qq_23157491的博客
02-24 634
解决方法：set @@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 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column
qq_37080417的博客
07-17 1692
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值