mysql - 使用 only_full_group_by

Question

我试图让下面的语句与 mysql v5.7.5 一起工作，尽管我真的完全不理解错误消息中的措辞，而且文档也没有帮助我。

有问题的 SQL 语句:

    SELECT 
        YEARWEEK(ts) AS YW, 
        DATE(ts) AS D,  
        SUM(`group-size`) AS `sum` 
    FROM `event` 
    GROUP BY YEARWEEK(ts);

我得到的错误:

Expression #2 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'event.ts' which is not functionally dependent on
columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

有人可以用英语解释我如何让它工作吗？对我来说，SQL 语句非常有意义，但它每次都抛出错误。

Answer 1

参见 longer answer :

简而言之，您需要将所有不包含聚合函数的列添加到GROUP BY中。在这种特殊情况下，需要添加“date(ds)”:

SELECT 
    YEARWEEK(ts) AS YW, 
    DATE(ts) AS D,  
    SUM(`group-size`) AS `sum` 
FROM `event` 
GROUP BY YEARWEEK(ts), DATE(ts);