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

select '店铺汇总' as product_id,
       concat(round((1-sum(cnt*in_price)/sum(cnt*price))*100,1),'%') profit_rate
FROM tb_order_detail t3
LEFT JOIN tb_product_info t1 ON  t3.product_id=t1.product_id
LEFT JOIN tb_order_overall t2 ON t3.order_id=t2.order_id
WHERE DATE_FORMAT(event_time,'%Y-%m')>='2021-10' AND shop_id='901'
GROUP BY shop_id
UNION
SELECT t3.product_id, 
    CONCAT(round((1-avg(in_price)/avg(price))*100,1),'%') profit_rate
FROM tb_order_detail t3
LEFT JOIN tb_product_info t1 ON  t3.product_id=t1.product_id
LEFT JOIN tb_order_overall t2 ON t3.order_id=t2.order_id
WHERE DATE_FORMAT(event_time,'%Y-%m')>='2021-10' AND shop_id='901'
GROUP BY product_id
HAVING TRIM(TRAILING '%' FROM profit_rate)>24.9;

简洁明了，注意TRIM函数的使用