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

WITH tmp AS (
     SELECT td.product_id,
        SUM(in_price*cnt) a_total,
        SUM(price*cnt) b_total
        FROM tb_order_detail td
        LEFT JOIN tb_order_overall tl ON td.order_id=tl.order_id 
        LEFT JOIN tb_product_info ti ON td.product_id=ti.product_id
        WHERE shop_id='901'AND DATE_FORMAT(tl.event_time,'%Y%m')>='202110'
        GROUP BY product_id 
)
SELECT '店铺汇总' AS product_id,
CONCAT(ROUND((1-SUM(a_total)/ SUM(b_total))*100,1),'%') profit_rate FROM tmp
UNION 
SELECT product_id,CONCAT(profit_rate,'%') profit_rate 
FROM
    (SELECT product_id,
    ROUND((1-a_total/b_total)*100,1) profit_rate
    FROM tmp
    HAVING profit_rate >24.9
    ORDER BY product_id)t2;