题解 | #某店铺的各商品毛利率及店铺整体毛利率#

# 1.问题：

请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率

# 2.思路：

1.店铺整理的毛利率

粒度：shop_id

店铺：901

商品毛利率>0.249

店铺整体毛利率：店铺毛利率=(1-总进价成本/总销售收入)*100%

总进价成本：以店铺为单位，进货价*进货数量

总销售输入：订单总额求和，条件是status=1

2.商品信息毛利率

粒度：product_id

店铺：901

商品毛利率>0.249

商品毛利率=(1-sum(进价数量)/sum(售价数量）)*100%

条件是status=1

3.利用union连接

# 3.代码

SELECT '店铺汇总' as product_id,concat(round((1-sum(a.in_price*c.cnt)/sum(c.price*c.cnt))*100,1),'%')
from tb_product_info a 
     left join tb_order_detail c on a.product_id=c.product_id
     left join tb_order_overall b on c.order_id=b.order_id
where shop_id=901 and b.event_time>='2021-10-01' and b.status=1
group by shop_id

union

select product_id,CONCAT(profit_rate,'%')
from(
select c.product_id product_id,round((1-sum(a.in_price*c.cnt)/(sum(c.price*c.cnt)))*100,1) profit_rate
from tb_product_info a 
    left join tb_order_detail c on a.product_id=c.product_id
    left join tb_order_overall b on c.order_id=b.order_id
where shop_id=901 and b.event_time>='2021-10-01' and b.status=1
group by product_id) aa
where profit_rate>24.9