如何编写SQL计算订单全商品适配的配送服务总费用

实现思路 #

核心逻辑是校验「配送服务覆盖的订单商品数」是否等于「订单总商品数」，只有相等的配送服务才满足全商品适配要求，具体步骤如下：

• 先统计每个订单包含的不同商品总数量
• 再关联配送服务与商品的绑定关系，统计每个配送服务在对应订单中可支持的商品数量
• 匹配上述两个统计结果，筛选出满足全商品适配的配送服务
• 最后关联配送服务成本表，对符合条件的服务成本求和得到订单总配送成本

注：如果业务规则要求多个符合条件的配送服务只取最低/最高成本，把最后的求和逻辑换成取最小值/最大值即可。

可直接运行的SQL示例 #

SELECT 
    o.order_id,
    COALESCE(SUM(ds.svc_cost), 0) AS total_delivery_cost
FROM `order` o
-- 统计每个订单的总商品数，作为适配校验基准
LEFT JOIN (
    SELECT order_id, COUNT(DISTINCT prod_id) AS total_prod_cnt
    FROM order_item
    GROUP BY order_id
) oi_total ON o.order_id = oi_total.order_id
-- 统计每个配送服务在对应订单中可支持的商品数
LEFT JOIN (
    SELECT 
        oi.order_id,
        sp.svc_name,
        COUNT(DISTINCT oi.prod_id) AS covered_prod_cnt
    FROM order_item oi
    JOIN service_prod sp ON oi.prod_id = sp.prod_id
    GROUP BY oi.order_id, sp.svc_name
) svc_cover ON o.order_id = svc_cover.order_id
-- 仅保留覆盖全部商品的配送服务
AND svc_cover.covered_prod_cnt = oi_total.total_prod_cnt
-- 关联配送服务成本表
LEFT JOIN delivery_service ds ON svc_cover.svc_name = ds.svc_name
GROUP BY o.order_id;

代码说明 #

• 子查询oi_total：统计每个订单包含的不同商品总数，作为适配校验的基准值
• 子查询svc_cover：统计每个订单下，每个配送服务可支持的商品数量
• 关联条件svc_cover.covered_prod_cnt = oi_total.total_prod_cnt：确保只有覆盖订单全部商品的配送服务才会被计入成本
• COALESCE函数：处理没有适配配送服务的订单，默认返回0成本，可根据业务需求调整为NULL或者其他默认值

内容的提问来源于stack exchange，提问作者Raf