题解 | 某店铺的各商品毛利率及店铺整体毛利率 吐血了！！

select product_id,concat(profit_rate,'%')
from (
select '店铺汇总' product_id, round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) as profit_rate,0 as flag
from tb_product_info t join tb_order_detail d
on t.product_id = d.product_id
join tb_order_overall o 
on d.order_id = o.order_id 
where shop_id = 901 
and status = 1 and event_time >= '2021-10-01'

union

select t.product_id,round((1-(sum(in_price*cnt)/sum(price*cnt)))*100,1) profit_rate,2 as flag
from tb_product_info t join tb_order_detail d
on t.product_id = d.product_id
join tb_order_overall o 
on d.order_id = o.order_id 
where shop_id = 901 
and status = 1 and event_time >= '2021-10-01'
group by t.product_id
having (1-(sum(in_price*cnt)/sum(price*cnt))) > 0.249)  tt
order by tt.flag