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

# 第一种解法 with rollup
select coalesce(t1.product_id,'店铺汇总') as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as  profit_rate
from tb_product_info t1
join tb_order_detail t3
on t1.product_id=t3.product_id
join tb_order_overall t2
on t2.order_id=t3.order_id
where date(t2.event_time) >= '2021-10-1'
and t1.shop_id='901'
group by t1.product_id with rollup 
having round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) > 24.9 or product_id IS NULL
order by t1.product_id

#第二种解法  union all
# with t as
# (select t1.product_id,t1.shop_id,t1.in_price,
# t2.order_id,t2.uid,t2.event_time,t2.total_amount,
# t3.price,t3.cnt
# from tb_product_info t1
# join tb_order_detail t3
# on t1.product_id=t3.product_id
# join tb_order_overall t2
# on t2.order_id=t3.order_id
# where date(t2.event_time) >= '2021-10-1'
# and t1.shop_id='901')

# select '店铺汇总' as product_id,
# concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as  profit_rate
# from t

# union all

# select product_id,
# concat(round((1-sum(in_price)/sum(price))*100,1),'%') as profit_rate
# from t
# group by 1
# having round((1-sum(in_price)/sum(price))*100,1) >24.9