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

# 生成店铺总体情况
select '店铺汇总'
,concat(round((1-sum(t2.in_price)/sum(t2.out_price))*100,1),'%') as profit_rate 
from (
select o1.order_id,o1.product_id,o1.price*o1.cnt as out_price
,o1.cnt*p1.in_price as in_price from 
tb_order_detail as o1 left join tb_product_info as p1 on 
o1.product_id=p1.product_id 
    left join tb_order_overall as o2 on o1.order_id=o2.order_id 
where p1.shop_id='901' and o2.event_time>='2021-10-01') t2
# 生成店铺中每个符合条件的产品的情况
union 
select * from (
select t1.product_id
,CONCAT(round((1-sum(t1.in_price)/sum(t1.out_price))*100,1),'%') as profit_rate 
from (
select o1.order_id,o1.product_id,o1.price*o1.cnt as out_price
,o1.cnt*p1.in_price as in_price from 
tb_order_detail as o1 left join tb_product_info as p1 on 
o1.product_id=p1.product_id 
        left join tb_order_overall as o2 on o1.order_id=o2.order_id 
where p1.shop_id='901' and o2.event_time>='2021-10-01' )
    t1 group by t1.product_id 
    having REPLACE(profit_rate,'%','')>24.9
    order by t1.product_id )t3