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

(
select '店铺汇总' as product_id,
concat(round((1 - (sum(in_price * cnt) / sum(price * cnt)))*100 , 1) ,'%') as profit_rate
from tb_order_detail as od
left join tb_order_overall as oo using(order_id)
left join tb_product_info as pi on pi.product_id = od.product_id
where year(event_time) = 2021 and month(event_time) >= 10
and shop_id = 901
and oo.status in (0,1)
)

# union all前后两段代码需加括号
union all

(
select od.product_id,
# wrong: concat((1-(pi.in_price/avg(od.price)))*100,'%') as profit_rate
concat((round((1-(pi.in_price / (sum(od.price*od.cnt) / sum(od.cnt))))*100 , 1)) , '%') as profit_rate
from tb_order_detail as od
left join tb_order_overall as oo using(order_id)
left join tb_product_info as pi on pi.product_id = od.product_id
where year(event_time) = 2021 and month(event_time) >= 10
and shop_id = 901
and oo.status in (0,1)
group by product_id
# replace函数
having replace(profit_rate,'%','') > 24.9
order by product_id
);