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

# 请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。
# tb_order_overall表里面的total_amount是错的，不能直接使用！！！！！
# 解题思路，首先将产品单价售价和店铺销售产品的总成本还有总销售额提取出来，然后再分别求出单产品的利润率和店铺的总利润率
with a as (
		select i.product_id,
		price,
		in_price,
		price * cnt amount_price,
		in_price * cnt pay
		from tb_product_info i, tb_order_detail a, tb_order_overall o
		where i.product_id = a.product_id and a.order_id = o.order_id
		and year(o.event_time) = 2021 and month(o.event_time) >= 10 and i.shop_id = 901),
b as(
select a.product_id , 1 - avg(a.in_price / a.price) profit_rate
from a 
group by a.product_id
having profit_rate > 0.249
)

select "店铺汇总" product_id, concat(round((1 - sum(pay)/sum(amount_price)) * 100, 1), "%") profit_rate
from a
UNION
(select product_id , concat(round(profit_rate * 100, 1),"%") profit_rate
from b
group by product_id
order by product_id asc)