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

with t1 as(
select t1.*,
t2.in_price from tb_order_detail t1 join tb_product_info t2 
on t1.product_id= t2.product_id
join tb_order_overall t3 on t1.order_id=t3.order_id
where t2.shop_id=901 and date(t3.event_time)>='2021-10-01'
),

t2 as(
select  product_id,
round(100*(1-sum(in_price*cnt)/sum(price*cnt)),1) as profit_rate
 from t1 group by product_id 
  with ROLLUP
	having profit_rate>=24.9 or product_id is null
  order by product_id 
	
	)
	
select ifnull(product_id,'店铺汇总') as product_id,concat(profit_rate,'%') as profit_rate from t2