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

select
'店铺汇总' as product_id, concat(round((1-sum(sum1_in)/sum(sum1_out))*100,1),'%') profit_rate 
from(
    select
    shop_id, product_id, sum(out_price) sum1_out, sum(tol_in_price) sum1_in
    from(
        select 
        p.shop_id, o.product_id, o.price*o.cnt out_price, p.in_price*o.cnt tol_in_price
        from
        tb_order_detail o left join tb_product_info p on o.product_id = p.product_id
        left join tb_order_overall a on o.order_id = a.order_id
        where p.shop_id = 901 and a.event_time >= '2021-10-01' and a.status = 1
    ) t
    group by product_id
) k
group by shop_id

union all

(select
product_id, concat(round((1-sum(sum1_in)/sum(sum1_out))*100,1),'%') profit_rate 
from(
    select
    shop_id, product_id, sum(out_price) sum1_out, sum(tol_in_price) sum1_in
    from(
        select 
        p.shop_id, o.product_id, o.price*o.cnt out_price, p.in_price*o.cnt tol_in_price
        from
        tb_order_detail o left join tb_product_info p on o.product_id = p.product_id
        left join tb_order_overall a on o.order_id = a.order_id
        where p.shop_id = 901 and a.event_time >= '2021-10-01' and a.status = 1
    ) t
    group by product_id
) k
group by product_id
having profit_rate > 24.9
order by product_id)