题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
店铺汇总与某个商品汇总的的查询逻辑不同,但是查询结果的表结构相同,所以此时可以用union
with t1 as ( select od.product_id, sum(price*cnt)as price,表示商品在这条订单中的总定价 pi.in_price*sum(cnt) as in_price,表示商品在这条订单中的总竞价 shop_id from tb_product_info pi right join tb_order_detail od on pi.product_id=od.product_id left join tb_order_overall oo on oo.order_id=od.order_id where date(event_time) >= '2021-10-01' and shop_id=901 and status=1 group by od.product_id,pi.in_price )三表连接,过滤条件 这里指的是店铺汇总那一行 select '店铺汇总' as product_id, concat(ROUND(ROUND(1-sum(in_price)/sum(price), 3)*100,1),'%') AS profit_rate from t1 group by shop_id union union进行连接 (select cast(product_id as char) product_id, concat(ROUND(ROUND(1-sum(in_price)/sum(price), 3)*100,1),'%') AS profit_rate from t1 group by product_id having ROUND(1-sum(in_price)/sum(price), 3) > 0.249 order by product_id); 总得来说感觉并不难。