不难,但是步骤繁琐,做题时要注意status,然后就是union和order by 的先后顺序了 在转变成百分比的时候括号特别多,不要少了
with tmp as (SELECT id,
sum(in_price*if(status=2,-cnt,cnt)) as all_in,
sum(price*if(status=2,-cnt,cnt)) as all_out
from (
select de.product_id as id,
in_price,
status,
price,
cnt
from
tb_order_detail de
left join
tb_order_overall ov
on de.order_id=ov.order_id
left join
tb_product_info info
on de.product_id=info.product_id
where DATE_FORMAT(event_time,"%Y-%m")>="2021-10"
and shop_id=901) base
group by id)
select "店铺汇总" as product_id,concat(round((1-sum(all_in)/sum(all_out))*100
,1),"%")
from tmp
union (
select id as product_id,concat(round((1-all_in/all_out)*100
,1),"%")
from tmp
where (1-all_in/all_out)>=0.249
order by product_id)