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

with aa as
    (select
        a.*,
        b.product_id,
        b.price,
        b.cnt,
        c.shop_id,
        c.in_price
    from
        tb_order_overall a 
    left join
        tb_order_detail b 
    on a.order_id=b.order_id
    left join
        tb_product_info c
    on b.product_id=c.product_id
    where 
        date_format(a.event_time,'%Y-%m')>='2021-10'
        and c.shop_id='901'
        and a.status =1
        and a.order_id not in (select order_id from tb_order_overall where status=2 )
    )

select
    '店铺汇总' product_id,
    concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') profit_rate
from
    aa
group by 
    shop_id
union all
(select 
    product_id,
    concat(round((1-avg(in_price)/(sum(price*cnt)/sum(cnt)))*100,1),'%') profit_rate
from
    aa
group by 
    product_id
having 
    replace(profit_rate,'%','')>=24.9
order by
    product_id)