原sql语句:
SELECT
info.sku_id,
ssav.attr_id,
ssav.attr_name,
ssav.attr_value
FROM `pms_sku_info` info
LEFT JOIN `pms_sku_sale_attr_value` ssav ON ssav.sku_id = info.sku_id
WHERE info.spu_id = 18
GROUP BY ssav.attr_id;
执行报错:
错误代码: 1055
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'gulimall_pms.info.sku_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
select表达式#4-info.sku_id不是聚合函数列,没有依赖group by 子句中的列,所以导致报错
当我们使用GroupBy时,要么我们所查询的字段在GroupBy后面都有,要么它们就是用分组函数聚合起来的
解决如下:
SELECT
ssav.attr_id,
ssav.attr_name,
GROUP_CONCAT(DISTINCT ssav.attr_value)
FROM `pms_sku_info` info
LEFT JOIN `pms_sku_sale_attr_value` ssav ON ssav.sku_id = info.sku_id
WHERE info.spu_id = 18
GROUP BY ssav.attr_id, ssav.attr_name;
我们根据ssav.attr_id, ssav.attr_name分组,这样就包含了要查询字段的两个,然后将ssav.attr_value进行组链接,即同一个分组下的所有ssav.attr_value放在一块,并通过DISTINCT去重。
效果如下: