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

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