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

with a as (
select t1.product_id,in_price*cnt ipcn,price*cnt pcn
from tb_order_detail t3 left join tb_product_info t1
on t3.product_id = t1.product_id
left join tb_order_overall t2
on  t3.order_id = t2.order_id
where date(event_time) >='2021-10-01'
and shop_id = 901
)
select '店铺汇总' product_id, concat(round(100*(1-sum(ipcn)/sum(pcn)),1),'%') profit_rat
from a
union
(select product_id,concat(profit_rat,'%') profit_rate from
(select product_id,round(100*(1-sum(ipcn)/sum(pcn)),1) profit_rat
from a
group by product_id) b
where profit_rat >24.9
order by profit_rat);