开启ONLY_FULL_GROUP_BY的MySQL 5.7分组查询报错的修正方案

这个问题很典型——ONLY_FULL_GROUP_BY是MySQL 5.7默认开启的严格模式，它要求查询逻辑必须完全清晰：SELECT列表里的非聚合列，以及ORDER BY中用到的非聚合列，要么出现在GROUP BY子句里，要么功能上依赖于GROUP BY的列（比如主键）。你的原查询有两个核心问题：

1. ORDER BY unix_time ASC：每个分组对应多条记录的unix_time值，数据库不知道该用哪个值来排序，违反了模式要求；
2. 虽然SELECT列是基于unix_time的函数计算，但排序字段的违规是触发错误的直接原因。

下面是针对按年周和按月分组的修正方案：

按年份周数分组的修正查询 #

方案1：直接修正GROUP BY和ORDER BY #

SELECT 
    CONCAT(YEAR(FROM_UNIXTIME(unix_time)), '-', WEEKOFYEAR(FROM_UNIXTIME(unix_time))) AS date,
    AVG(price_usd) AS model,
    reference.product_name -- 如果你需要获取产品名称，记得加上这个字段
FROM price_avg
INNER JOIN reference ON reference.id_reference = price_avg.id_reference
WHERE price_avg.id_reference = 1
GROUP BY 
    YEAR(FROM_UNIXTIME(unix_time)),
    WEEKOFYEAR(FROM_UNIXTIME(unix_time)),
    price_avg.id_reference,
    reference.product_name -- 产品名称是非聚合列，必须加入GROUP BY
ORDER BY 
    YEAR(FROM_UNIXTIME(unix_time)) ASC,
    WEEKOFYEAR(FROM_UNIXTIME(unix_time)) ASC;

方案2：用子查询简化重复计算 #

如果不想重复写时间转换函数，可以先在子查询里处理好时间格式，再做分组：

SELECT 
    CONCAT(sub.year_val, '-', sub.week_val) AS date,
    AVG(sub.price_usd) AS model,
    ref.product_name
FROM (
    SELECT 
        price_usd,
        id_reference,
        YEAR(FROM_UNIXTIME(unix_time)) AS year_val,
        WEEKOFYEAR(FROM_UNIXTIME(unix_time)) AS week_val
    FROM price_avg
    WHERE id_reference = 1
) AS sub
INNER JOIN reference ref ON ref.id_reference = sub.id_reference
GROUP BY 
    sub.year_val,
    sub.week_val,
    sub.id_reference,
    ref.product_name
ORDER BY 
    sub.year_val ASC,
    sub.week_val ASC;

按月份分组的修正查询 #

同理，按月分组只需要把时间转换改成月份格式即可：

SELECT 
    DATE_FORMAT(FROM_UNIXTIME(unix_time), '%Y-%m') AS date,
    AVG(price_usd) AS model,
    reference.product_name
FROM price_avg
INNER JOIN reference ON reference.id_reference = price_avg.id_reference
WHERE price_avg.id_reference = 1
GROUP BY 
    DATE_FORMAT(FROM_UNIXTIME(unix_time), '%Y-%m'),
    price_avg.id_reference,
    reference.product_name
ORDER BY 
    DATE_FORMAT(FROM_UNIXTIME(unix_time), '%Y-%m') ASC;

关键注意点 #

• 如果你不需要产品名称，直接去掉SELECT和GROUP BY里的reference.product_name即可；
• 排序时一定要用分组的唯一标识（比如年+周、年+月），或者用聚合后的时间值（比如MIN(unix_time)），绝对不能用未聚合的原始unix_time；
• MySQL 5.7不支持在GROUP BY里直接使用SELECT里的别名，所以要么重复写表达式，要么用子查询来规避。

内容的提问来源于stack exchange，提问作者albaserver