题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
思路: 连表后根据shop_id分组, 然后求毛利率, 不同的是既要求某个商品的的毛利率, 又需要求该店铺的毛利率, 两种方法
- 使用with rollup(貌似只有mysql8.0支持), 该函数的功能是分组汇总统计, 举个例子, 根据product_id分组, 则会在分组的最后一行, 增加一行汇总行, 当有多个分组时, 则会在每个分组增加一行汇总数据
- 两次查询然后合并, 先根据product_id分组, 查出毛利率, 再根据shop_id分组, 查该店的毛利率. 然后用union合并
- 注意点1: 毛利率大于24.9%, 这个在查询后是字符串类型, 但是可以在having中使用, 进行比较
- 注意点2: having中的or product_id is null过滤, 是因为with rollup中的product_id汇总列是空值, 所以需要增加一个or条件进行判定
having (1 - sum(t1.in_price * t1.cnt) / sum(t1.price * t1.cnt)) > 0.249
select ifnull(t1.product_id, '店铺汇总') as product_id, concat(round((1 - sum(t1.in_price * t1.cnt) / sum(t1.price * t1.cnt)) * 100, 1), '%') profit_rate from ( select pi.shop_id, pi.product_id, pi.in_price, od.price, od.cnt from tb_order_overall oo left join tb_order_detail od on oo.order_id=od.order_id left join tb_product_info pi on od.product_id=pi.product_id where oo.event_time >= '2021-10-01' and pi.shop_id=901 ) t1 group by t1.product_id with rollup having (1 - sum(t1.in_price * t1.cnt) / sum(t1.price * t1.cnt)) > 0.249 or product_id is null order by product_id