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

select
product_id
,concat(profit_rate,'%')  as profit_rate
from
(select
'店铺汇总' as product_id
,round((1-sum(b.in_price*a.cnt)/sum(a.price*a.cnt))*100,1) as profit_rate
from tb_order_detail a
left join tb_product_info b
on a.product_id = b.product_id
left join tb_order_overall c
on a.order_id = c.order_id
where b.shop_id=901
and date(event_time)>'2021-10-01'

union

select
a.product_id as product_id
,round((1-sum(b.in_price*a.cnt)/sum(a.price*a.cnt))*100,1) as profit_rate
from tb_order_detail a
left join tb_product_info b
on a.product_id = b.product_id
left join tb_order_overall c
on a.order_id = c.order_id
where b.shop_id=901
and date(event_time)>'2021-10-01'
group by a.product_id
having profit_rate>24.9
) t