72、商城业务---Mysql报错---Group By使用注意事项

原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去重。

效果如下：