Mysql解决SELECT list is not in GROUP BY clause and contains nonaggregated column 问题

最新推荐文章于 2025-03-06 21:09:40 发布
最新推荐文章于 2025-03-06 21:09:40 发布
阅读量1.6k 收藏 2
点赞数 1
分类专栏： 数据库 文章标签： Mysql nonaggregated GROUP BY  only_full_group_by
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/u013538390/article/details/103921057
版权

解决办法：

三步走，实测有效！！！

1：--查看数据库sql查询模式

select @@GLOBAL.sql_mode;

2：-- 去除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';

3：重启自身项目重新加载数据库配置即可!

原因：

MYSQL5.7.x版本默认开启了only_full_group_by 查询模式，不允许需要group by的字段后跟其他字段。

如果想要保留该模式，可以尝试在需要group by字段的后边使用any_value(其他字段)，这个方式参考网友答案，个人未经尝试

mysql报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
DN金猿的博客
06-28 1042
经过查询资料发现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，去掉即可。
[Linux 完美解决]Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column
m0_48296376的博客
03-24 2219
错误 Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 一、 其他参考文档 临时解决方法: Mysql解决SELECT list is not in GROUP BY clause and contains nonaggregated column 问题 另一个: mysql遇见Expression #1 of SELECT list is not in GROUP BY clau
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column...原因及解决方案
江海寄余生的博客
03-30 1万+
最近公司新建了一个项目，在项目中需要用到group by进行聚合计算，在计算的同时也要查出一些其他字段来返回给前端。于是就有了这个错误的出现。 先简单复现我所写的sql，其实sql非常简单。 select channel_name as channelName, brand_name as brandName, sum(actual_value) as actualValue, sum(actual_value_ty) a
mysql中SELECT list is not in GROUP BY clause and contains nonaggregated column解决办法
weixin_45810161的博客
01-02 3056
Expression #1 of 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 nonaggregated column“
最新发布
总结复盘，才能不迷失自己！
03-06 757
1.问题描述MySQL 中出现 “1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column” 错误？
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
A乐神的博客
12-15 8424
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
SELECT list is not in GROUP BY clause and contains nonaggregated column
qq_42334297的博客
09-10 8818
SELECT list is not in GROUP BY clause and contains nonaggregated column问题解决方案单次解决修改配置文件使用规范的sql语句 问题 在单独部署项目的时候遇到了这个问题，具体意思就是： SELECT列表不在GROUP BY子句中，且包含非聚合列 意思应该就是SELECT的属性必须出现在GROUP BY语句中，即类似于SELECT a.name FROM user a GROUP BY a.name这种样子。 问题根源应该是sql语句的问题，
【解决】 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
MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic
weixin_63716356的博客
07-24 2083
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使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.
weixin_40925480的博客
11-03 1206
mysql使用group by查询报错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 1341
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 SELECT list is not in GROUP BY clause and contains nonaggregated column
opfan的博客
11-05 800
安装了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 i...
MySQL：SELECT list is not in GROUP BY clause 报错 解决方案
换条路吧，编程没意思的~
06-20 5791
MySQL 报错：SELECT list is not in GROUP BY clause and contains nonaggregated column 解决方案
SELECT list is not in GROUP BY clause and contains nonaggregated column解决和总结
热门推荐
ITxiaofeixiang的博客
04-12 4万+
问题描述 查询语句 SELECT name,sum(class_id) from res GROUP BY class_id 结果 SELECT list is not in GROUP BY clause and contains nonaggregated column ‘dt.res.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=o
MySQL报错:SELECT list is not in GROUP BY clause and contains nonaggregated column,解决ONLY_FULL_GROUP_BY
myyw001的博客
09-11 1652
3.去掉ONLY_FULL_GROUP_BY ，其他参数不变，执行即可。2.查看返回信息是否包含ONLY_FULL_GROUP_BY。1.查看.sql_mode配置。
MYSQL 错误解决 SELECT list is not in GROUP BY clause and contains nonaggregated column
lbuskeep的专栏
11-07 4216
温馨提示：1、如果是 linux 操作系统在/etc/mysql/conf.d/mysql.cnf 下做修改 2、如果是 windows 操作系统在 mysql 目录下修改 mysql.ini 修改操作： 1、进入文件后在 [mysqld] 最后一行加以下代码 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,...
MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法
m0_67882861的博客
11-07 5144
因为sno属性是主码，即primary key，经测试发现group by **，**为主码的属性名时，select语句中的查询列没有在group by中出现，也是允许的。红色部分可以发现sc.sno与studen.sno都没有在GROUP BY的列中，但执行语句依旧正常，因为使用了聚集函数。执行mysql语句，select 选择的列不仅仅只有sno，为什么正常输出？存书（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）现有两表Student表以及SC表(选课表)。student表结构。
MySQL报错 SELECT list is not in GROUP BY clause and contains nonaggregated column...
Main_1125的博客
01-15 9217
MySQL报错 SELECT list is not in GROUP BY clause and contains nonaggregated column… 原因： 在mysql5.7以上的版本中，对于 group by 的这种聚合操作，如果在select 中的列，没有在group by 中出现，那么这个SQL是不合法的，因为列不在group by的从句中，所以对于设置了这个mode的数据库，在使用group by 的时候，就要用MAX()，SUM()，ANT_VALUE()的这种聚合函数，才能完成GR
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column
07-27
回答: 当使用GROUP BY语句进行查询时，SELECT语句中只能包含GROUP BY后面声明的列或者聚合函数。这是因为MySQL的ONLY_FULL_GROUP_BY模式要求SELECT语句中的列必须是明确的，以确保分组求最值的合法性检查。这种模式采用了与Oracle、DB2等数据库的处理方式。因此，如果在GROUP BY语句中使用了聚合函数，就需要将SELECT语句中的列限制在GROUP BY后面声明的列或者聚合函数中。如果SELECT语句中出现了不明确的列，就会报错"SELECT list is not in GROUP BY clause and contains nonaggregated column"。\[1\]\[2\]\[3\] #### 引用[.reference_title] - *1* [mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.](https://blog.youkuaiyun.com/weixin_40925480/article/details/127680083)[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^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column......](https://blog.youkuaiyun.com/weixin_37557729/article/details/123896433)[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^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值