题解 | 某店铺的各商品毛利率及店铺整体毛利率

#总店铺
select '店铺汇总' as product_id,
concat(round((1-sum(tp.in_price*td.cnt)/sum(td.price*td.cnt))*100,1),'%') as profit_rate
from tb_order_detail td
join tb_order_overall too
on td.order_id = too.order_id
join tb_product_info tp
on tp.product_id = td.product_id
where tp.shop_id = 901
and year(too.event_time) >= 2021
and month(too.event_time)>=10
and status =1
union

#单个产品
select 
product_id,profit_rate
from(
select td.product_id,
1-sum(tp.in_price*td.cnt)/sum(td.price*td.cnt) as xianzhi,
concat(round((1-sum(tp.in_price*td.cnt)/sum(td.price*td.cnt))*100,1),'%') as profit_rate
from tb_order_detail td
join tb_order_overall too
on td.order_id = too.order_id
join tb_product_info tp
on tp.product_id = td.product_id
where tp.shop_id = 901
and year(too.event_time) >= 2021
and month(too.event_time)>=10
and status =1
group by td.product_id
)a
where xianzhi>0.249

通过union合并店铺结果和商品结果

注意1）同一商品的售价不一定相同；2）成本不是总进货数*进价，而是卖出的数量*进价