题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
# 先将2021年10月以来,901店铺的信息找到 with t1 as ( select product_id, in_price from tb_product_info where shop_id = 901 ), t2 as ( # 找到2021-10及之后已经付款的订单的商品销售以及采购详细信息 select a.product_id, sum(a.price * a.cnt) as total_cost, sum(t1.in_price * a.cnt) as total_account from t1 right join ( select order_id, product_id, price, cnt from tb_order_detail where order_id in ( select order_id from tb_order_overall where date_format(event_time, '%Y-%m') >= '2021-10' and status = 1 ) and product_id in (select product_id from t1) ) as a on a.product_id = t1.product_id group by a.product_id ) # 计算每一个商品的毛利率以及店铺总体毛利率 select '店铺汇总' as product_id, concat(format((1-sum(total_account)/sum(total_cost))*100, 1), '%') as profit_rate from t2 union all select * from ( select product_id, concat(format((1-total_account/total_cost)*100, 1), '%') as profit_rate from t2 where format((1-total_account/total_cost)*100, 1) > 24.9 order by product_id ) as e;