如何在MariaDB中按区间查询获取商品最低配送成本

背景与需求 #

现有数据库包含以下表，需编写SQL查询，针对指定市场区域和商品ID列表，根据商品的delivery_cost_unit值匹配delivery_costs表中的对应成本区间，最终得到每个商品的最低配送成本：

• products：核心列：id（商品ID）、delivery_cost_unit（配送成本单位值）
• delivery_methods：核心列：id（配送方式ID）、visible（是否可见，仅需考虑可见的配送方式）
• delivery_costs：按配送方式、市场区域划分成本区间，核心列：delivery_method_id（关联配送方式ID）、market_area_id（关联市场区域ID）、min_delivery_cost_units（区间下限）、delivery_cost（对应区间的配送成本）
• market_areas：核心列：id（区域ID）、name（区域名称）

遇到的问题 #

之前的查询无法正确匹配成本区间，例如delivery_cost_unit=300的商品，对应delivery_method_id=88的配送成本应为1490，但查询错误返回了999（匹配了更低的无效区间）。

环境与期望结果 #

• 数据库版本：MariaDB v10.6.4
• 期望输出：包含product_id（商品ID）、cheapest_delivery_cost（最低配送成本），可选包含cheapest_delivery_method_id（对应最低成本的配送方式ID）

核心思路 #

要正确匹配区间，需为每个商品+配送方式组合，找到**min_delivery_cost_units小于等于商品delivery_cost_unit的所有区间中，最大的那个下限对应的配送成本**——因为成本区间是按下限递增划分的，最大的符合条件的下限才是商品实际所属的区间。

完整SQL查询 #

WITH matched_delivery_costs AS (
    SELECT
        p.id AS product_id,
        dc.delivery_method_id,
        dc.delivery_cost,
        -- 为每个商品+配送方式组合，按区间下限降序排序，取最匹配的区间
        ROW_NUMBER() OVER (
            PARTITION BY p.id, dc.delivery_method_id
            ORDER BY dc.min_delivery_cost_units DESC
        ) AS rn
    FROM products p
    -- 替换为目标商品ID列表
    WHERE p.id IN (1, 2, 3)
    -- 关联配送成本表，匹配指定市场区域+符合条件的区间
    JOIN delivery_costs dc 
        ON dc.min_delivery_cost_units <= p.delivery_cost_unit
        AND dc.market_area_id = 10 -- 替换为目标市场区域ID
    -- 关联配送方式表，仅保留可见的配送方式
    JOIN delivery_methods dm 
        ON dm.id = dc.delivery_method_id
        AND dm.visible = 1
)
SELECT
    product_id,
    MIN(delivery_cost) AS cheapest_delivery_cost,
    -- 可选：获取对应最低成本的配送方式ID（成本相同时取ID最小的）
    FIRST_VALUE(delivery_method_id) OVER (
        PARTITION BY product_id
        ORDER BY delivery_cost ASC, delivery_method_id ASC
    ) AS cheapest_delivery_method_id
FROM matched_delivery_costs
-- 只保留每个商品+配送方式组合的正确匹配区间
WHERE rn = 1
GROUP BY product_id;

代码说明 #

1. CTE matched_delivery_costs：

   • 筛选指定的商品ID和市场区域，关联配送成本表与配送方式表（过滤不可见的配送方式）
   • 通过ROW_NUMBER()按min_delivery_cost_units降序排序，确保每个商品+配送方式组合仅保留最匹配的区间记录（rn=1）

2. 最终查询：

   • 对每个商品，从匹配到的正确区间成本中取最小值，得到最低配送成本
   • 可选通过FIRST_VALUE()获取对应最低成本的配送方式ID，若多个方式成本相同，优先取ID较小的

示例验证 #

针对delivery_cost_unit=300的商品，若delivery_method_id=88的区间数据如下：

查询会保留min_delivery_cost_units=201的记录（rn=1），最终计算最低成本时会取1490，解决区间匹配错误的问题。

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