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

#毛利率=（1-进价/售价）*100%  with rollup
#其实一直有个疑问，从给出的表2和表3来看，吊牌价（表3的价格）*件数!=表2中的的对应的order_id 对应的价格，所以不清楚这个过程中是不是存在类似于满减这种操作，如果是的话那商品的售价其实！=吊牌价（price),以下的解法就存在问题，希望有经验的大牛们给一个解答，让我死心，非常感谢~。。。。


select
    product_id,
    concat(profit_rate,'%') as profit_rate
from 
(
    select
        ifnull(product_id,'店铺汇总') as product_id,
        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 as t1
        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'
        and status=1
    ) as a
    group by product_id
    with rollup
    having profit_rate>24.9 or product_id is null
    order by product_id
) as b