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

# 店铺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。

1. 用temp表将有效的（时间、对象等condition）明细筛选出来
2. 完成每一个业务逻辑的查询再union

一定要注意多表join之后是否会出现重复的行？是否需要distinct剔除？