题解|百分比作为筛选条件 #各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
# ### 2021年10月以来，
# DATE_FORMAT(event_time,'%Y-%m') >= '2021-10'
(SELECT '店铺汇总' AS product_id,
CONCAT(ROUND((1 - SUM(in_price*cnt)/SUM(price*cnt))*100,1),'%') AS profit_rate
FROM tb_order_detail a
LEFT JOIN tb_order_overall b ON a.order_id = b.order_id
LEFT JOIN tb_product_info c ON a.product_id = c.product_id
WHERE DATE_FORMAT(event_time,'%Y-%m') >= '2021-10' AND shop_id = '901')
UNION
(SELECT product_id,profit_rate
FROM
(SELECT a.product_id AS product_id,
CONCAT(ROUND((1 - SUM(in_price*cnt)/SUM(price*cnt))*100,1),'%') AS profit_rate
FROM tb_order_detail a
LEFT JOIN tb_order_overall b ON a.order_id = b.order_id
LEFT JOIN tb_product_info c ON a.product_id = c.product_id
WHERE DATE_FORMAT(event_time,'%Y-%m') >= '2021-10' AND shop_id = '901'
GROUP BY a.product_id
ORDER BY a.product_id )t1
HAVING replace(profit_rate,'%','') > 24.9)
