栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

MySQL报错: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

MySQL报错: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

这个错误主要是 select 的内容里的字段没有再group by 中出现

这里提供两种解决办法:

第一种:

在你的MySQL的my.ini文件里面加一条

[mysqld]
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
 

第二种:

我本来用的是

SELECT
 *
FROM
 biz_table bt
 LEFT JOIN biz_table_ver btv ON bt.table_id = btv.table_id 
WHERe
 bt.system_up = 1 
GROUP BY
 bt.table_name

这种写法报错

 Expression #1 of SELECt list is not in GROUP BY clause and contains nonaggregated column 'zx_ds.biz_table_ver.ver_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byn### The error may exist in URL [jar:file:/opt/zxs/ds/ds-dict.jar!/BOOT-INF/classes!/mapper/system/BizTableMapper.xml]n### The error may involve com.gyjn.dict.data.mapper.BizTableMapper.selectBizTableList-Inlinen### The error occurred while setting parametersn### SQL: select         *        FROM         biz_table  bt         LEFT JOIN ( SELECt * FROM biz_table_ver GROUP BY table_id ) btv ON bt.table_id = btv.table_id          WHERe  bt.system_up=1n### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECt list is not in GROUP BY clause and contains nonaggregated column 'zx_ds.biz_table_ver.ver_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byn; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zx_ds.biz_table_ver.ver_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

后来改为

SELECT
	* 
FROM
	biz_table_ver v
	INNER JOIN biz_table t
	INNER JOIN ( SELECt max( ver_id ) ver_id, table_id FROM biz_table_ver GROUP BY table_id ) maxv ON v.ver_id = maxv.ver_id 
	AND t.table_id = maxv.table_id

就可以了

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/985838.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号