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

SELECT 
    '店铺汇总' product_id,
    CONCAT(
        ROUND((1 - SUM(OD.cnt * PI.in_price) / SUM(OD.price * OD.cnt))*100, 1), '%'
    ) profit_rate
FROM tb_order_detail OD,tb_order_overall OO, tb_product_info PI
WHERE OD.order_id = OO.order_id
    AND OD.product_id = PI.product_id
    AND DATE_FORMAT(OO.event_time,'%Y-%m') >= '2021-10' 
    AND PI.shop_id =901
    AND OO.status = 1
UNION
SELECT
    t.product_id product_id,
    CONCAT(ROUND(t.profit_rate, 1), '%') profit_rate
FROM(
    SELECT 
        OD.product_id product_id,
        (1 - SUM(OD.cnt * PI.in_price) / SUM(OD.price * OD.cnt))*100 profit_rate
    FROM tb_order_detail OD,tb_order_overall OO, tb_product_info PI
    WHERE OD.order_id = OO.order_id
        AND OD.product_id = PI.product_id
        AND DATE_FORMAT(OO.event_time,'%Y-%m') >= '2021-10' 
        AND PI.shop_id =901
        AND OO.status = 1
    GROUP BY OD.product_id
    HAVING profit_rate > 24.9
) t