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

with temp as (
    select 
        a.product_id,
        a.in_price,
        b.total_amount,
        b.total_cnt,
        c.cnt,
        c.price,
        status
    from tb_product_info as a 
    left join tb_order_detail as c 
    on a.product_id = c.product_id
    left join tb_order_overall as b 
    on c.order_id = b.order_id
    where shop_id = 901
    and substr(event_time,1,7) >= '2021-10'
)

select 
    '店铺汇总' as product_id,
    CONCAT(round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100, 1), '%')  as profit_rate
from temp
union
select 
    product_id,
    CONCAT(round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100, 1), '%')  as profit_rate
from temp
group by 
    product_id
having round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100, 1) > 24.9