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

select   product_id,CONCAT(profit_rate, '%')
from (
        select IFNULL(product_id,'店铺汇总') as product_id ,round((1-sum(inPrice)/sum(outPrice))*100,1) as profit_rate
        from (
            select e1.product_id,e2.price*e2.cnt as outPrice,e1.in_price*e2.cnt as inPrice
            from 
            (
            select product_id,in_price
            from tb_product_info
            where shop_id=901
            ) e1
            join 
            (
            select product_id,price,cnt
            from tb_order_detail
            where order_id in
                (select order_id
                from tb_order_overall
                where event_time>='2021-10-01' and status=1)
            ) e2
            on e1.product_id=e2.product_id
        )e3

        group by product_id
        with rollup

        HAVING profit_rate > 24.9 OR product_id IS NULL

        order by product_id

) e4