hanchuanchuan
创建视图时报 SELECT list is not in GROUP BY clause and contains nonaggregated column
描述
创建视图
create or replace view test_dba as SELECT name,round(sum(score),2) as score from test group by name ;
检测时报错
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ROUND(SUM(score), 2)' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
重现 create table test (id int auto_increment PRIMARY key ,name VARCHAR(10) , score DECIMAL(5,3)) ;
insert into test(name,score) values('张三',66.789); insert into test(name,score) values('张三',77.789); insert into test(name,score) values('张三',88.789); insert into test(name,score) values('李四',99.789); insert into test(name,score) values('李四',90.789);
create or replace view test_dba as SELECT name,round(sum(score),2) as score from test group by name ; 点击检测时报错
环境
- 数据库: [阿里云 RDS MySQL]
- 版本: [例如 8.0.28]
参数 inception 的sql_mode ="" ，跟随目标实例
