> 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxx.create_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

🌞 建议反馈 产品需求
1

TiDb版本:4.04

问题描述:

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tb1.create_date’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

语句如下:
select DATE_ADD(DATE_FORMAT(create_date,‘%Y-%m-%d’),INTERVAL 2 Day) ,count(1) from tb1 where create_date>‘2020-09-01’ GROUP BY DATE_FORMAT(create_date,‘%Y-%m-%d’)

以上语句会报 group by错误

但是 select DATE_ADD(create_date,INTERVAL 2 Day) ,count(1) from tb1 where create_date>‘2020-09-01’ GROUP BY create_date
又是可正常得到结果

select DATE_FORMAT(create_date,‘%Y-%m-%d’) ,count(1) from tb1 where create_date>‘2020-09-01’ GROUP BY DATE_FORMAT(create_date,‘%Y-%m-%d’)

也可得到正确结果

请问是有什么限制么?

2
  1. 麻烦上传下表结构，我们测试一下，多谢。
  2. 反馈下 sql_mode 当前配置
3

SELECT @@sql_mode;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

4

表结构:
CREATE TABLE tb1 (
id int(11) NOT NULL AUTO_INCREMENT,
create_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
userid int(11) NOT NULL DEFAULT 0,
amount int(11) DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001;

5

  1. 测试了下，mysql 会报同样的错

    image
    image1480×133 88.4 KB

  2. 可以修改为： select DATE_ADD(DATE_FORMAT(create_date,‘%Y-%m-%d’),INTERVAL 2 Day) ,count(1) from tb1 where create_date>‘2020-09-01’ GROUP BY DATE_ADD(DATE_FORMAT(create_date,‘%Y-%m-%d’),INTERVAL 2 Day); 试试

6

OK 我没测试在mysql上,抱歉,你这个我晓得,再加一个子查询也是可以的

7

:+1:

8

请大神帮忙看下报错原因

9

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。