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

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