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

陈一一同学

已于 2022-06-21 17:42:56 修改

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 sql mysql

于 2022-06-21 17:30:27 首次发布

本文链接：https://blog.csdn.net/m0_57666466/article/details/125395289

本文介绍了如何修复MySQL中因非聚合字段出现在SELECT列表且未包含在GROUP BY子句中的错误，提供了修改SQL语句的两种方法，包括配置sql_mode参数和调整JOIN操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这个错误主要是 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_by\n### 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-Inline\n### The error occurred while setting parameters\n### 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=1\n### 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_by\n; 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

就可以了