MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法
作者:mmseoamin日期:2023-12-21

报错原因

使用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表(选课表)。

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第1张

执行语句

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

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

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第2张

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

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

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第3张

解决方法

临时解决(重启mysqld后失效)

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

select @@global.sql_mode;

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第4张

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

插入结果

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第5张

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第6张

求解问题

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

方法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;

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第7张

查询出所有的sql_mode

select @@global.sql_mode;

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第8张

移除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';

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第9张

重启mysql后,执行语句

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第10张

查询出所有的sql_mode

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第11张

发现没有ONLY_FULL_GROUP_BY

重启mysqld后,再次查询sql_mode

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第12张

发现有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;

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第13张

思考

问题

student表结构

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第14张

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

select * from student group by sno;

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法,第15张

答案

因为sno属性是主码,即primary key,经测试发现group by **,**为主码的属性名时,select语句中的查询列没有在group by中出现,也是允许的。