按查询分组在严格ONLY_FULL_Group _by SQL模式下不起作用

Question

我有一个逐组查询，它在PHP PDO中运行良好，但当我在mysql.ini中添加"sql_mode=ONLY_FULL_group _by"时，相同的查询失败，并出现错误

SQLSTATE[42000]：语法错误或访问冲突：1055"db.table.t_date"不在GROUP BY 中

实际查询

SELECT 
    * 
FROM 
(
    SELECT 
        DATE_FORMAT(t_date, '%b') AS mon, 
        DATE_FORMAT(t_date, '%Y-%m') AS fy_date, 
        (SUM(val1) - SUM(val2)) AS net_val 
    FROM 
        TABLE 
    GROUP BY 
        YEAR(t_date), 
        MONTH(t_date) 
    ORDER BY 
        DATE_FORMAT(t_date, '%Y-%m') DESC 
    LIMIT 12
) AS tbl 
ORDER BY 
    fy_date

有人能告诉我为什么上面的查询不适用于吗

sql_mode = ONLY_FULL_GROUP_BY

Answer 1

您需要将两个date_format表达式都添加到group by，因为所有非聚合列都需要分组。

SELECT 
    DATE_FORMAT(t_date, '%b') AS mon, 
    DATE_FORMAT(t_date, '%Y-%m') AS fy_date, 
    (SUM(val1) - SUM(val2)) AS net_val 
FROM 
    TABLE 
GROUP BY 
    DATE_FORMAT(t_date, '%b'), 
    DATE_FORMAT(t_date, '%Y-%m') 
ORDER BY 
    DATE_FORMAT(t_date, '%Y-%m') DESC