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

不难，但是步骤繁琐，做题时要注意status，然后就是union和order by 的先后顺序了 在转变成百分比的时候括号特别多，不要少了

 with tmp as  (SELECT id,
       sum(in_price*if(status=2,-cnt,cnt)) as all_in,
       sum(price*if(status=2,-cnt,cnt)) as all_out
 from (
select de.product_id as id,
       in_price,
       status,
       price,
       cnt
from 
tb_order_detail de
left join 
tb_order_overall  ov
on de.order_id=ov.order_id
left join 
tb_product_info info
on de.product_id=info.product_id
where DATE_FORMAT(event_time,"%Y-%m")>="2021-10"
      and shop_id=901) base
                group by id)
      
select "店铺汇总" as product_id,concat(round((1-sum(all_in)/sum(all_out))*100
                            ,1),"%")
from tmp
union (
select id as product_id,concat(round((1-all_in/all_out)*100
                            ,1),"%")
from tmp
where (1-all_in/all_out)>=0.249
order by product_id)