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

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

比较无脑的解法，卡住的点是having必须放orderby之前