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 ;