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

select 
    '店铺汇总' as product_id,
    concat(round((1-sum(b.in_price*a.cnt)/sum(a.price*a.cnt))*100,1),'%') as profit_rate
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 
    date_format(c.event_time,'%Y%m') >='202110'
    and b.shop_id = '901'

union all

select *
from(
select 
    a.product_id as product_id,
    concat(round((1-max(b.in_price)/(sum(a.price*a.cnt)/sum(a.cnt)))*100,1),'%') as profit_rate
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 
    date_format(c.event_time,'%Y%m') >='202110'
    and b.shop_id = '901'
group by 
    a.product_id
having 
    round((1-max(b.in_price)/(sum(a.price*a.cnt)/sum(a.cnt)))*100,1)>24.9
order by product_id
) t1