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

思路：

1. 毛利率=1-总进价/总售价

• 计算公式=1-sum(in_price * cnt)/sum(price * cnt)
• 百分数形式（concat()）

2. 店铺毛利率和商品毛利率的区别

• 店铺——group by shop_id
• 商品——group by product_id 故需要分别取两类毛利率再以union/union all合并

3. 排序

• 若以" select … from … union select… from… order by" ，最后的order by 对整合后的表格起作用，如此一来”店铺汇总“会到最后一行。
• 若想解决这一问题，则需要order by 只对第二个表格起作用，也就是把第二个表格和order by括起来，再用select from

代码

select '店铺汇总' as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_order_detail as tod
join tb_product_info as tpi
on tpi.product_id=tod.product_id
join tb_order_overall as too
on tod.order_id=too.order_id
where shop_id=901 and event_time>='2021-10-01 00:00:00'
group by shop_id

union

select * from
(select tod.product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_order_detail as tod
join tb_product_info as tpi
on tpi.product_id=tod.product_id
join tb_order_overall as too
on tod.order_id=too.order_id
where shop_id=901 and event_time>='2021-10-01 00:00:00'
group by product_id
having 1-sum(in_price*cnt)/sum(price*cnt)>0.249
order by product_id) as q