题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
with info as ( SELECT pi.product_id,ov.order_id,in_price,price,cnt FROM tb_product_info pi INNER JOIN tb_order_detail od ON pi.product_id = od.product_id INNER JOIN tb_order_overall ov ON ov.order_id = od.order_id WHERE date(event_time) >= "2021-10-01" AND shop_id = 901 ) select '店铺汇总' as product_id, concat( round(( 1 - sum(in_price*cnt) / sum(price * cnt) )*100, 1), '%') from info union all select product_id, concat( round(( 1 - max(in_price) / (sum(price * cnt)/ sum(cnt)) )*100, 1), '%') from info where product_id in ( select product_id from info group by product_id having ( 1 - max(in_price) / (sum(price * cnt)/ sum(cnt)) )*100 > 24.9 ) GROUP BY product_id