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

select
product_id
,profit_rate
from
(
        select
        info.product_id as product_id
        ,concat(round((1 - sum(in_price) / sum(price)) * 100, 1), '%') as profit_rate
        from tb_order_detail detail
        left join tb_order_overall overall
        on detail.order_id = overall.order_id
        left join tb_product_info info
        on detail.product_id = info.product_id
        where info.shop_id = '901' 
        and left(overall.event_time, 7) >= '2021-10'
        and status != 2 and status != '2'
        group by product_id
        having (1 - sum(in_price) / sum(price)) > 0.249

        union all

        select
        '店铺汇总' as product_id
        ,concat(round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100, 1), '%') as profit_rate
        from tb_order_detail detail
        left join tb_order_overall overall
        on detail.order_id = overall.order_id
        left join tb_product_info info
        on detail.product_id = info.product_id
        where info.shop_id = '901' 
        and left(overall.event_time, 7) >= '2021-10'
        and status != 2 and status != '2'
        group by product_id
) a
order by 
    case when product_id = '店铺汇总' then 0 else 1 end, 
    product_id;

自己写的时候踩了很多坑，需要重做。