题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
http://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
全部答案
(select
'店铺汇总' as product_id,
concat(round((1-sum(t2.in_price*t1.cnt)/sum(t1.price*t1.cnt))*100,1),'%') as profit_rate
from
tb_order_detail t1 left join
tb_product_info t2 on
t1.product_id = t2.product_id left join
tb_order_overall t3 on
t1.order_id = t3.order_id
where year(event_time) >=2021
and month(event_time) >= 10
and t2.shop_id=901
group by product_id)
union all
(select
t1.product_id as product_id,
concat(round((1-sum(t2.in_price*t1.cnt)/sum(t1.price*t1.cnt))*100,1),'%') as profit_rate
from
tb_order_detail t1 left join
tb_product_info t2 on
t1.product_id = t2.product_id left join
tb_order_overall t3 on
t1.order_id = t3.order_id
where year(event_time) >=2021
and month(event_time) >= 10
and t2.shop_id=901
group by t1.product_id
having ((1-sum(t2.in_price*t1.cnt)/sum(t1.price*t1.cnt))*100) > 24.9
order by t1.product_id)
