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

WITH a AS(
    SELECT 
    tb_order_detail.order_id,
    tb_order_detail.product_id,
    tb_order_detail.price,
    tb_order_detail.cnt,
    tb_order_overall.event_time
    FROM tb_order_detail
    JOIN tb_order_overall USING(order_id)
    WHERE DATE_FORMAT(tb_order_overall.event_time,'%Y-%m') >= '2021-10'
    AND tb_order_overall.status != 2
),
b AS(
    SELECT
    product_id,
    SUM(price*cnt) zongxiaoshou,
    SUM(cnt) maichu,
    SUM(price*cnt) / SUM(cnt) pingjun
    FROM a
    GROUP BY product_id
)

SELECT '店铺汇总' AS product_id, CONCAT(ROUND((1-SUM(pp.in_price*b.maichu)/SUM(zongxiaoshou))*100,1),'%')
FROM b
JOIN tb_product_info pp USING(product_id)
WHERE pp.shop_id = 901 
UNION ALL
SELECT b.product_id, CONCAT(ROUND((1-pp.in_price/b.pingjun)*100,1),'%') profit_rate
FROM b
JOIN tb_product_info pp USING(product_id)
WHERE pp.shop_id = 901  AND ROUND((1-pp.in_price/b.pingjun)*100,1) >24.9