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

select '店铺汇总' as product_id,
       concat(round((1-sum(cnt*in_price)/sum(cnt*price))*100,1),'%') profit_rate
from tb_order_detail a
left join tb_product_info b on a.product_id = b.product_id
left join tb_order_overall c on a.order_id = c.order_id
where (shop_id = 901) 
      and (date(event_time)>='2021-10-01')
union
select a.product_id,
       concat(round((1-in_price/(avg(price)))*100,1),'%') profit_rate
from tb_order_detail a
left join tb_product_info b
on a.product_id = b.product_id
where shop_id = 901
GROUP BY a.product_id
having trim(TRAILING '%' from profit_rate) > 24.9