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

#### 店铺汇总需要按照放在第一张表；具体的商品ID放在第二张表；统计店铺汇总按shop_id 分组，统计1-所有的商品的总进价/所有商品的总销售额，即为该商铺的毛利率；统计按商品ID product_id分组，统计1-商品单进价/商品单销售额 ，即为该商品的毛利率。
     select '店铺汇总' product_id,
             concat(round((1-sum(ti.in_price*td.cnt)/sum(td.price*td.cnt))*100,1),'%')
        from tb_product_info ti 
        join tb_order_detail td using(product_id) 
        join tb_order_overall too using(order_id) 
       where ti.shop_id = '901' 
         and date_format(too.event_time,'%Y%m') >= '202110' 
         and too.status = 1 
       group by ti.shop_id 
      union 
     select * from (
     select ti.product_id ,
            concat(round((1-ti.in_price/avg(td.price))*100,1),'%')  profit_rate 
       from tb_product_info ti 
       join tb_order_detail td using(product_id) 
       join tb_order_overall too using(order_id) 
      where ti.shop_id = '901' 
        and date_format(too.event_time,'%Y%m') >= '202110' 
        and too.status = 1 
      group by ti.product_id,ti.in_price   
      having round((1-ti.in_price/avg(td.price))*100,1) > 24.9
      order by ti.product_id 
     ) t ;