MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

报错原因

使用GROUP BY 语句违背了sql_mode=only_full_group_by，在MySQL数据库版本为5.7以上的版本，默认开启了 ONLY_FULL_GROUP_BY SQL模式，在此模式下，对于group by操作,如果在select语句中的查询列没有在group by中出现，那么这个SQL就是非法的，因为列不在group by语句中，所以设置了sql_mode=only_full_group_by的数据库，在使用group by时就会报错，换句话说，拒绝选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列的查询，这些列既不在 GROUP BY 子句中命名，也不在功能上依赖于（唯一确定的）GROUP BY 列。

注意

不是说SELECT xx，xx必须是GROUP BY中的列，如SELECT 聚集函数(不在GROUP中的列)也能正常执行

例如

现有两表Student表以及SC表(选课表)。

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

执行语句

select cno,count(sc.sno),count(student.sno) from student,sc group by cno;

红色部分可以发现sc.sno与studen.sno都没有在GROUP BY的列中，但执行语句依旧正常，因为使用了聚集函数。

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

而不把sc.sno放在聚集函数中，则执行错误。

select cno,sc.sno from student,sc group by cno;

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

解决方法

临时解决（重启mysqld后失效）

1.直接在mysql-cli层面做设置

select @@global.sql_mode;

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

2.将ONLY_FULL_GROUP_BY从sql_mode中移除：

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

3.重启MySQL客户端

注意不要关闭mysqld，否则修改会复原。

在MySQL-server层面修改sql_mode

永久生效，重启依然有效

优化mysql语句

使用派生表查询

例子

BOOKSTORAGE表：

存书（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）

CREATE TABLE BOOKSTORAGE(
isbn VARCHAR(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
publisher VARCHAR(10), 
edition VARCHAR(10),
date DATE,
writer VARCHAR(10),
price FLOAT CHECK(price>=0),
pprice FLOAT CHECK(pprice>=0),
number INT CHECK(number>=0)
);

销售（日期，书号，数量，单价）

CREATE TABLE SALE(
date DATE,
isbn VARCHAR(10),
number INT CHECK(number>=0),
price FLOAT CHECK(price>=0),
FOREIGN KEY(isbn) REFERENCES BOOKSTORAGE(isbn)
);

注意先建立BOOKSTORAGE表再建立SALE表，因为SALE表外键参考BOOKSTORAGE，所以要先建立BOOKSTORAGE

插入数据

INSERT
INTO BOOKSTORAGE
VALUES('1','西游记','人民文学出版社','第三版','2003/1/1','吴承恩',20.1,15.5,150),
('2','水浒传','人民文学出版社','第二版','2005/9/1','施耐庵',23.9,17.1,200),
('3','三国演义','中华书局出版社','第五版','2008/6/1','罗贯中',26.3,15.8,210),
('4','红楼梦','人民文学出版社','第四版','2001/6/1','曹雪芹',22.3,17.2,190);

INSERT
INTO SALE
VALUES('2023-3-12','1','30',17.8),
('2023-3-15','1','20',20.1),
('2023-3-12','2','25',18.8),
('2023-3-15','2','25',23.9),
('2023-3-12','3','15',18.6),
('2023-3-15','3','30',26.3),
('2023-3-12','4','22',19.5),
('2023-3-15','4','12',22.3);

注意先插入BOOKSTORAGE，因为SALE参考BOOKSTORAGE

插入结果

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

求解问题

列出所有日期的销售报表，包括书名、数量和合计金额（每一种书的销售总金额）

方法1修改sql_mode

mysql语句

SELECT SALE.date,BOOKSTORAGE.name,SALE.number,ROUND(SUM(SALE.price*SALE.number),2) money
FROM SALE,BOOKSTORAGE 
WHERE SALE.isbn=BOOKSTORAGE.isbn
GROUP BY SALE.isbn,SALE.DATE 
ORDER BY SALE.date;

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

查询出所有的sql_mode

select @@global.sql_mode;

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

移除ONLY_FULL_GROUP_BY

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

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

重启mysql后，执行语句

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

查询出所有的sql_mode

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

发现没有ONLY_FULL_GROUP_BY

重启mysqld后，再次查询sql_mode

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

发现有ONLY_FULL_GROUP_BY

方法2优化mysql语句

SELECT SALE2.DATE,name,SALE2.number,SALE2.PRICE
FROM BOOKSTORAGE,(SELECT isbn,date,SUM(number),ROUND(SUM(price*number),2) FROM SALE GROUP BY SALE.isbn,SALE.DATE) AS SALE2(isbn,date,number,price)
WHERE SALE2.isbn=BOOKSTORAGE.isbn
ORDER BY SALE2.DATE ASC;

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

思考

问题

student表结构

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

执行mysql语句，select 选择的列不仅仅只有sno，为什么正常输出？

select * from student group by sno;

of select list is not in group by clause and contains nonaggregated column,个人经验,mysql,数据库

答案

因为sno属性是主码，即primary key，经测试发现group by **，**为主码的属性名时，select语句中的查询列没有在group by中出现，也是允许的。文章来源地址https://www.toymoban.com/news/detail-764659.html

到了这里，关于MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！