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

with a as
(
    select product_id, order_id, in_price, cnt, price
    from tb_order_detail
    join tb_order_overall using(order_id)
    join tb_product_info using(product_id)
    where date_format(event_time,'%Y-%m')>='2021-10' and shop_id=901
)

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