题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
WITH t AS( SELECT shop_id,d.product_id,in_price,cnt,price FROM tb_order_overall o JOIN tb_order_detail d ON o.order_id=d.order_id JOIN tb_product_info i ON d.product_id=i.product_id WHERE DATE(event_time)>='2021-10-01' AND status = 1 AND shop_id = 901 ) (SELECT '店铺汇总' produt_id, CONCAT(ROUND(100*(1-SUM(cnt*in_price)/SUM(cnt*price)),1),'%') profit_rate FROM t GROUP BY shop_id) UNION (SELECT product_id, CONCAT(ROUND(100*(1-AVG(in_price)*SUM(cnt)/SUM(cnt*price)),1),'%') profit_rate FROM t GROUP BY product_id HAVING LEFT(profit_rate,3)>24.9 ORDER BY product_id )