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

select *
from (
select '店铺汇总' product_id
,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') profit_rate
from tb_product_info
left join tb_order_detail
on tb_product_info.product_id=tb_order_detail.product_id
left join tb_order_overall
on tb_order_detail.order_id=tb_order_overall.order_id
where shop_id='901'
and date_format(event_time,'%Y-%m')>='2021-10'
and status!=2
union all
select tb_product_info.product_id product_id
,concat(round((1-avg(in_price)/(sum(price*cnt)/sum(cnt)))*100,1),'%') profit_rate
from tb_product_info
left join tb_order_detail
on tb_product_info.product_id=tb_order_detail.product_id
left join tb_order_overall
on tb_order_detail.order_id=tb_order_overall.order_id
where shop_id='901'
and date_format(event_time,'%Y-%m')>='2021-10'
and status!=2
group by tb_product_info.product_id
having (1-avg(in_price)/(sum(price*cnt)/sum(cnt)))*100>=24.9)a