题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
刚开始忽略了having round(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)),1)>24.9 or product_id is null 中product_id is null 这个条件，一直通过不了。后来想到having在select前执行，又使用了with Rollup 会出现一个null 值，如果只判定>24.9就把null值过滤掉了，所以这个条件必须加。还有个有些人可能会忽略的问题，判定>24.9要用数值去比，不能直接having profit_rate>'24.9%'
select
ifnull(c.product_id,'店铺汇总')product_id,
concat(round(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)),1),'%')as profit_rate
from tb_order_overall a
inner join tb_order_detail b on a.order_id=b.order_id
inner join tb_product_info c on b.product_id=c.product_id
where date_format(event_time,'%Y-%m')>='2021-10' and shop_id='901'
group by c.product_id
with rollup
having round(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)),1)>24.9 or product_id is null
order by c.product_id
#TPLINK秋招提前批#
select
ifnull(c.product_id,'店铺汇总')product_id,
concat(round(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)),1),'%')as profit_rate
from tb_order_overall a
inner join tb_order_detail b on a.order_id=b.order_id
inner join tb_product_info c on b.product_id=c.product_id
where date_format(event_time,'%Y-%m')>='2021-10' and shop_id='901'
group by c.product_id
with rollup
having round(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)),1)>24.9 or product_id is null
order by c.product_id
哔哩哔哩公司福利 596人发布
