mysql查询字段与group by 字段：Expression #2 of SELECT list is not in GROUP BY clause

1.报错示例

1-1 执行sql语句：

SELECT
    ssav.attr_id attr_id,
    ssav.attr_name attr_name,
    ssav.attr_value,
    GROUP_CONCAT(DISTINCT info.sku_id) sku_ids
FROM
    pms_sku_info info
LEFT JOIN pms_sku_sale_attr_value ssav ON ssav.sku_id = info.sku_id
WHERE
    info.spu_id = 13
GROUP BY
    ssav.attr_id

1-2 报错：

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

2.报错原因分析

2-1 原因：

因为MySQL的ONLY_FULL_GROUP_BY模式启用，这意味着所有在SELECT列表中的列都必须在GROUP BY子句中明确列出，或者与GROUP BY子句中的列通过某种方式相关联。如果不这样做，MySQL将拒绝执行查询，以防止返回不一致的结果集。

3.解决

3-1 将查询的字段写入group by中(根据业务需要类似于info.sku_id一样，使用聚合函数将查询的字段包装一下，这样不需要写入group by中了)

SELECT
    ssav.attr_id attr_id,
    ssav.attr_name attr_name,
    ssav.attr_value,
    GROUP_CONCAT(DISTINCT info.sku_id) sku_ids
FROM
    pms_sku_info info
LEFT JOIN pms_sku_sale_attr_value ssav ON ssav.sku_id = info.sku_id
WHERE
    info.spu_id = 13
GROUP BY
    ssav.attr_id, ssav.attr_name,ssav.attr_value;