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

select  
    ifnull(t1.product_id,'店铺汇总') as product_id,
    concat(round(100*(1-sum(in_price*cnt)/sum(price*cnt)),1),'%') as profit_rate 
from tb_product_info  as t1
    join tb_order_detail using(product_id)
    join tb_order_overall using(order_id)
where shop_id='901' and date(event_time)>='2021-10-01'#2021年10月以来店铺901
group by t1.product_id
with rollup
 having(substring_index(profit_rate,'%',1)>24.9) or product_id IS NULL  #毛利率大于24.9%及店铺整体毛利率
order by product_id #按商品ID升序输出各商品毛利率