题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
http://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
# 生成店铺总体情况
select '店铺汇总'
,concat(round((1-sum(t2.in_price)/sum(t2.out_price))*100,1),'%') as profit_rate
from (
select o1.order_id,o1.product_id,o1.price*o1.cnt as out_price
,o1.cnt*p1.in_price as in_price from
tb_order_detail as o1 left join tb_product_info as p1 on
o1.product_id=p1.product_id
left join tb_order_overall as o2 on o1.order_id=o2.order_id
where p1.shop_id='901' and o2.event_time>='2021-10-01') t2
# 生成店铺中每个符合条件的产品的情况
union
select * from (
select t1.product_id
,CONCAT(round((1-sum(t1.in_price)/sum(t1.out_price))*100,1),'%') as profit_rate
from (
select o1.order_id,o1.product_id,o1.price*o1.cnt as out_price
,o1.cnt*p1.in_price as in_price from
tb_order_detail as o1 left join tb_product_info as p1 on
o1.product_id=p1.product_id
left join tb_order_overall as o2 on o1.order_id=o2.order_id
where p1.shop_id='901' and o2.event_time>='2021-10-01' )
t1 group by t1.product_id
having REPLACE(profit_rate,'%','')>24.9
order by t1.product_id )t3
