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

select '店铺汇总' product_id, 
concat(round((1 - sum(in_price * cnt)/sum(price * cnt))*100,1),'%') profit_rate
from tb_product_info a 
inner join tb_order_detail b 
on a. product_id = b.product_id 
inner join tb_order_overall c 
on b.order_id = c.order_id
where a.shop_id = 901
and status = 1
and event_time >= '2021-10-01'
union
(select product_id,concat(profit_rate,'%') profit_rate
from (select a.product_id, 
round((1 - sum(in_price * cnt)/sum(price * cnt))*100,1) profit_rate
from tb_product_info a 
inner join tb_order_detail b 
on a. product_id = b.product_id 
inner join tb_order_overall c 
on b.order_id = c.order_id
where a.shop_id = 901
group by product_id) u
where profit_rate > 24.9)