题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
with temp as (
select t1.product_id,
sum(cnt*in_price) as total_in ,
sum(price*cnt) as total_sale
from tb_product_info t1
left join tb_order_detail t2
on t1.product_id = t2.product_id
left join tb_order_overall t3
on t2.order_id=t3.order_id
where t1.shop_id=901 and date_format(t3.event_time,'%Y-%m') >= '2021-10' and status=1
group by t1.product_id)
select '店铺汇总' as product_id,
concat(round((1-sum(total_in)/sum(total_sale))*100,1),'%') as profit_rate
from temp
union
select * from(
select product_id,
concat(round((1-total_in/total_sale)*100,1),'%') as profit_rate
from temp
having LEFT(profit_rate,CHAR_LENGTH(profit_rate)-1)>24.9
order by product_id) ttemp
错犯：
#2021年10月以来 店铺901中所有商品的信息 计算毛利率 select COALESCE(t1.product_id, '店铺汇总') AS product_id,concat(round((1-sum(cnt*in_price)/sum(price*cnt))*100,1),'%') as profit_rate from tb_product_info t1 left join tb_order_detail t2 on t1.product_id = t2.product_id left join tb_order_overall t3 on t2.order_id=t3.order_id where t1.shop_id=901 and date_format(t3.event_time,'%Y-%m') >= '2021-10' and status=1 group by t1.product_id with rollup having LEFT(profit_rate,CHAR_LENGTH(profit_rate)-1)>24.9 order by t1.product_id
1、万一店铺的毛利率低于24.9，就会被过滤了，所以不用with rollup,用union
2、知识点2（来自某位大师）:使用union()函数，order by只能在最后使用一次
如需要对各部分分别排序，代码如下
select *from
(select *from tablel order by a desc)aa
union all
select *from
(select *from table2 order by b)bb
注:union可以使用任何select的语句，包括having
3、每个中间表都要有别称-ttemp
4、比率转化成百分比：num*100 再round,再concat %
5、比较大小时：用left 提取除了最后一位的字符
6、审题 小心是21年10月以来还是 21年10月
