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

with a as (select tod.product_id,tod.price,in_price,tod.cnt
from tb_order_overall too,tb_order_detail tod,tb_product_info tpi
where too.order_id=tod.order_id and tpi.product_id=tod.product_id
and date(event_time)>='2021-10-01' 
and shop_id='901'
)
select '店铺汇总' as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from a
union
select product_id,
concat(round((1-avg(in_price)/avg(price) )*100,1),'%') as profit_rate
from a
group by product_id
having (1-avg(in_price)/avg(price) )*100>=24.9