题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
这个题目有点不是很严谨的感觉~
参考题解
WITH tb_info AS (SELECT order_id,product_id,price,cnt,in_price FROM (SELECT order_id,uid FROM tb_order_overall WHERE date_format(event_time,'%Y%m')>='202110')tb_order_overall INNER JOIN tb_order_detail USING(order_id) INNER JOIN (SELECT product_id,in_price FROM tb_product_info WHERE shop_id=901)tb_product_info USING(product_id)) SELECT '店铺汇总' product_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate FROM tb_info UNION SELECT product_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate FROM tb_info GROUP BY product_id HAVING round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) > 24.9;
运行结果
