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

WITH total AS (
SELECT 
product_id,
in_price,
price,
cnt

FROM tb_order_detail AS od
LEFT JOIN tb_order_overall AS oo
    USING(order_id)
LEFT JOIN tb_product_info AS po
    USING(product_id)
    
WHERE 
shop_id = '901' AND
event_time > '2021-10-01' 
)

SELECT
'店铺汇总' AS product_id,
CONCAT(ROUND((1-SUM(in_price * cnt)/SUM(price * cnt)) * 100, 1), '%') AS profit_rate

FROM total

UNION 

SELECT
product_id,
CONCAT(ROUND((1-SUM(in_price * cnt)/SUM(price * cnt)) * 100, 1), '%') AS profit_rate

FROM total

GROUP BY product_id

HAVING (1-SUM(in_price * cnt)/SUM(price * cnt)) * 100 > 24.9