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

SELECT product_id,
    CONCAT(profit_rate, '%') AS profit_rate
FROM (
    SELECT COALESCE(product_id, '店铺汇总') AS product_id,
        ROUND((1 - SUM(in_price * pro_total_cnt) / SUM(price * pro_total_cnt)) * 100, 1) AS profit_rate
    FROM (
        SELECT product_id,
            in_price,
            price,
            SUM(cnt) AS pro_total_cnt
        FROM tb_order_overall
            JOIN tb_order_detail
            USING(order_id)
            JOIN tb_product_info
            USING(product_id)
        WHERE status = 1
            AND shop_id = 901
            AND DATE(event_time) >= '2021-10-01'
        GROUP BY product_id, in_price, price
    ) total_product_t
    GROUP BY product_id
    WITH ROLLUP
    HAVING profit_rate > 24.9
        OR product_id IS NULL
    ORDER BY product_id
)t