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

执行语句

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

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

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

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

解决方法

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

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

select @@global.sql_mode;

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

插入结果

求解问题

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

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

查询出所有的sql_mode

select @@global.sql_mode;

移除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后，执行语句

查询出所有的sql_mode

发现没有ONLY_FULL_GROUP_BY

重启mysqld后，再次查询sql_mode

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

思考

问题

student表结构

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

select * from student group by sno;

答案

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