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

SELECT IFNULL(product_id, '店铺汇总') as product_id,
        concat(ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1),'%') as profit_rate
    FROM (
        SELECT product_id, price, cnt, in_price
        FROM tb_order_detail
        JOIN tb_product_info USING(product_id)
        JOIN tb_order_overall USING(order_id)
        WHERE shop_id = 901 and DATE(event_time) >= "2021-10-01"
    ) as t_product_in_each_order
    GROUP BY product_id
    WITH ROLLUP
    HAVING 1 - SUM(in_price*cnt) / SUM(price*cnt) > 0.249 OR product_id IS NULL
    ORDER BY product_id

#加OR product_id IS NULL  是为了防止having>0.249的筛选条件把店铺毛利小于0.24筛选掉。 如果店铺的毛利为0.14，商品只有8001为0.29大于0.249，其余商品都小于0.249，店铺的总体毛利率会被拉低。

删掉OR product_id IS NULL   结果如下：

法二：更简单。 一个union

select
   "店铺汇总" product_id,
    concat(round((1-sum(tpi.in_price * cnt)/sum(tod.price * cnt))*100,1),'%') as profit_rate
from
    tb_order_detail tod left join tb_order_overall too on tod.order_id = too.order_id
    left join tb_product_info tpi on  tod.product_id = tpi.product_id
where
    shop_id = 901 and DATE_FORMAT(event_time,'%Y-%m') between '2021-10' and DATE_FORMAT(now(),'%Y-%m')
group by
    shop_id

union

select
    *
from (
    select
    tod.product_id as product_id,
    concat(round((1-sum(tpi.in_price * cnt)/sum(tod.price * cnt))*100,1),'%') as profit_rate
from
    tb_order_detail tod left join tb_order_overall too on tod.order_id = too.order_id
    left join tb_product_info tpi on  tod.product_id = tpi.product_id
where
    shop_id = 901 and DATE_FORMAT(event_time,'%Y-%m') between '2021-10' and DATE_FORMAT(now(),'%Y-%m')   #这里不能用profit_rate > 0.249，都没计算出来结果。 都没聚合呢。
group by
    shop_id,tod.product_id
having 
    round(1-sum(tpi.in_price * cnt)/sum(tod.price * cnt),3) > 0.249  #这里也不用profit_rate，因为加了%没法比大小
order by
    tod.product_id ) t

可优化：

2021年10月以来 --date(event_time)>'20211001'