group_concat去重之后拼接字段内容group_concat(DISTINCT a.color SEPARATOR ‘--‘)

 

 

SELECT a.good_id '商品编号',b.`name` as '商品名称',a.color as '颜色',a.size as '大小尺寸',CONCAT(a.color,a.size) '颜色大小',a.cost as '进价',a.sale as '售价',a.lirui as '利润',DATE_FORMAT(a.create_time,'%Y-%m-%h') '售卖时间',DATE_FORMAT(a.create_time,'%Y-%m') '售卖月份' FROM `good_sale` a INNER JOIN good_info b on a.good_id=b.id;

SELECT a.good_id '商品编号',b.`name` as '商品名称',a.color as '颜色',a.size as '大小尺寸',concat_ws('-',a.color,a.size) '颜色大小', a.cost as '进价',a.sale as '售价',a.lirui as '利润',DATE_FORMAT(a.create_time,'%Y-%m-%h') '售卖时间',DATE_FORMAT(a.create_time,'%Y-%m') '售卖月份' FROM `good_sale` a INNER JOIN good_info b on a.good_id=b.id where a.good_id=2;

-- 统计各个商品的利润情况
SELECT a.good_id '商品编号',b.`name` as '商品名称', a.color as '爆款颜色',a.size  as '爆款尺寸',a.cost as '进价',a.sale as '售价',sum(a.lirui) as '利润' ,count(a.id)'销售数量' FROM `good_sale` a INNER JOIN good_info b on a.good_id=b.id
GROUP BY a.good_id   ;
-- 统计各个商品的颜色
SELECT a.good_id '商品编号',b.`name` as '商品名称',group_concat(DISTINCT a.color SEPARATOR '--') '所有已售颜色', a.color as '爆款颜色',group_concat(DISTINCT a.size SEPARATOR '--') '所有已售尺寸' ,a.size   as '爆款尺寸',a.cost as '进价',a.sale as '售价',sum(a.lirui) as '利润'  FROM `good_sale` a INNER JOIN good_info b on a.good_id=b.id
GROUP BY a.good_id  ;