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

select
    coalesce(product_id,'店铺汇总') product_id,
    concat(round(profit_rate,1),'%') profit_rate
from (
select
    a.product_id,
    (1-sum(b.in_price*a.cnt)/sum(a.price*a.cnt))*100 profit_rate
from tb_order_detail a
inner join tb_product_info b on a.product_id = b.product_id and b.shop_id = 901
inner join tb_order_overall c on a.order_id = c.order_id and c.event_time >= '2021-10-01'
group by product_id
with rollup
having profit_rate > 24.9 or a.product_id is null
) t
order by field(product_id, '店铺汇总', product_id)