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

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