题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
问题：
请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。
注：商品毛利率=(1-进价/平均单件售价)*100%；
店铺毛利率=(1-总进价成本/总销售收入)*100%。
结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。
题解：
注意表之间的关系：
-- order_id 对应的是一个订单多个商品，相同的product_id价格不一定相同
-- tb_order_overall 里的total_amount涉及多个产品，不能用于计算
-- tb_product_info 每一个商品的进货价都是唯一的
-- tb_order_overall 里的total_amount涉及多个产品，不能用于计算
-- tb_product_info 每一个商品的进货价都是唯一的
法一：union
把 group by 条件去掉，就是店铺汇总
select '店铺汇总',concat(round((1-sum(cnt*in_price)/sum(cnt*price))*100,1),'%') from tb_order_detail as od left join tb_product_info as p on od.product_id = p.product_id left join tb_order_overall as o on od.order_id = o.order_id where date_format(o.event_time,"%Y%m")>=202110 and shop_id= 901 union (select od.product_id as product_id ,concat(round((1-sum(cnt*in_price)/sum(cnt*price))*100,1),'%') as profit_rate from tb_order_detail as od left join tb_product_info as p on od.product_id = p.product_id left join tb_order_overall as o on od.order_id = o.order_id where date_format(o.event_time,"%Y%m")>=202110 and p.shop_id= 901 group by od.product_id having (1-sum(cnt*in_price)/sum(cnt*price))*100>24.9 order by product_id)
法二：with rollup
with rollup:在group分组字段的基础上二次统计数据
coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
select coalesce(product_id,'店铺汇总') as product_id ,concat(profit_rate,'%') from( select od.product_id as product_id ,round((1-sum(cnt*in_price)/sum(cnt*price))*100,1) as profit_rate from tb_order_detail as od left join tb_product_info as p on od.product_id = p.product_id left join tb_order_overall as o on od.order_id = o.order_id where date_format(o.event_time,"%Y%m")>=202110 and p.shop_id= 901 group by od.product_id with rollup having (1-sum(cnt*in_price)/sum(cnt*price))*100>24.9 or product_id is null ) a order by product_id<>'店铺汇总'
百度公司福利 605人发布
投递百度等公司6个岗位 >
