SELECT list is not in GROUP BY clause and contains nonaggregated column

最新推荐文章于 2024-08-09 12:44:01 发布
最新推荐文章于 2024-08-09 12:44:01 发布
阅读量1.1k 收藏
点赞数 1
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lihua5419/article/details/108098878
版权

首先打开数据库客户端

错误信息如：

nested exception is java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'digitao.API1.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因是因为高版本的配置原因，和低版本不兼容，总之是因为sql_mode=only_full_group_by，先解决问题吧

在数据库中执行以下命令：

select @@global.sql_mode;

会得到以下结果集：会有一个ONLY_FULL_GROUP_BY结果，删除它即可

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

可以直接执行SET命令修改，如果set失败

也可以简单粗暴地方法，直接修改mysql的配置文件本地修改my.ini、linux修改my.cnf文件，

在[mysqld]下增加一行如下参数：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

最后记得重启mysql服务

linux服务器重启命令为：

service mysqld restart

关于linux上mysql的操作命令后续会出详细的文章整理出来，感谢关注和支持

MyXieZhiXin
关注
专栏目录
mysql报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
DN金猿的博客
06-28 244
经过查询资料发现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，去掉即可。
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.
weixin_40925480的博客
11-03 1111
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.
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报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解
最新发布
科技改变人类，技术成就未来
08-09 1211
在使用 MySQL 时，可能会遇到这样的错误提示：“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column”。这通常是由于在 GROUP BY 查询中，SELECT 列表中的某些列没有被包含在 GROUP BY 子句中或没有使用聚合函数所导致的。本文将详细介绍这一错误的原因，并提供相应的解决方法。
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
A乐神的博客
12-15 7679
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
丑小鸭
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中SELECT list is not in GROUP BY clause and contains nonaggregated column解决办法
weixin_45810161的博客
01-02 2477
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 1275
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
[Linux 完美解决]Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column
m0_48296376的博客
03-24 2143
错误 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...
weixin_43697366的博客
06-08 630
今天接口突然返回这个错误： 因为一直使用的是阿里云 rds实例，之前接口返回正常，没有更新过代码，因为最近重新购买了该实例rds,所以问题定位到这个上边，后来查询是mysql的配置是默认了参数的sql_mode=only_full_group_by MySql在低版本（5.7.x以下）中允许select后面的非聚合列不出现在group by中。以下sql在低版本中是可以运行的，但是在5.7及以上版本会报错 通过该命令查询：SELECT @@sql_mode 解决方法就是在对应rds实例化的.
MySQL报错 SELECT list is not in GROUP BY clause and contains nonaggregated column...
Main_1125的博客
01-15 8759
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：SELECT list is not in GROUP BY clause 报错 解决方案
换条路吧，编程没意思的~
06-20 2804
MySQL 报错：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
opfan的博客
11-05 707
安装了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...
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
sql报错SELECT list is not in GROUP BY clause and contains nonaggregated column
qq_40262372的博客
06-24 9798
高频面经汇总：https://blog.csdn.net/qq_40262372/article/details/116075528 一、问题分析 其中错误的原因是说group by哪一句中的select的字段是不对的。 二、情景还原 dept_emp表： salaries表： 我想去查询每个部门人员薪水，于是写下了这样的sql: select de.dept_no ,s.salary from dept_emp de left join salaries s on..
【错误解决】SELECT list is not in GROUP BY clause and contains nonaggregated column
小猿备忘录
03-01 1979
错误描述：问题出现在本人在搭建项目开发环境的时候，新安装的mysql-2.7.29默认开启了only_full_group_by模式，导致group by相关语句报错。 解决方法： 在mysql的my.cnf配置文件中加入如下配置既可解决： sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIV...
mysql报错SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx‘.
Gblfy_Blog
10-14 1093
mysql报错SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx‘.
select list is not in group by clause and contains nonaggregated column
06-28
综上所述，产生 select list is not in group by clause and contains nonaggregated column 错误的主要原因是 GROUP BY 子句和未被聚合的列之间不匹配，需要保证 select list 中的列都参与到 GROUP BY 子句或聚合...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值