题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
# 店铺901在2021年10月以来的所有明细 with temp as ( select t.order_id, product_id, price, cnt, in_price from( select order_id, tb_order_detail.product_id, price, cnt, in_price from tb_order_detail left join tb_product_info on tb_product_info.product_id = tb_order_detail.product_id where shop_id = 901 )t left join tb_order_overall on tb_order_overall.order_id = t.order_id where year(event_time)=2021 and month(event_time)>=10 and status=1 ) # 1. 计算店铺整体毛利率 (select '店铺汇总', concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate from temp) union # 2. 毛利率大于24.9%的商品信息 (select distinct temp.product_id, concat(round((1-in_price/avg_price)*100,1),'%') as profit_rate from ( select product_id, sum(price*cnt)/sum(cnt) as avg_price from temp group by product_id ) t1 left join temp on t1.product_id = temp.product_id where round((1-in_price/avg_price)*100,1)>24.9)
复杂的业务逻辑一定要慢慢仔细捋清思路,注意不要漏掉任何condition。
- 用temp表将有效的(时间、对象等condition)明细筛选出来
- 完成每一个业务逻辑的查询再union
一定要注意多表join之后是否会出现重复的行?是否需要distinct剔除?