Mix and Match场景：为购物车商品应用最优低价优惠的SQL查询问询

业务需求 #

• 用户添加商品到购物车后，需检查可应用的Mix and Match优惠
• 同一商品对应多个优惠时，选择价格最低的优惠应用
• 单个商品仅能应用一项优惠，但商品数量超出该优惠要求时，剩余数量可适配其他优惠

当前SQL语句 #

SELECT
    GROUP_CONCAT(tl.tl_id) lineIds,
    mm.mm_offer,
    SUM(tl.tl_price * tl.tl_quantity) AS originalPrice,
    mm.mm_offerPrice,
    (
        SUM(tl.tl_price * tl.tl_quantity) - mm.mm_offerPrice
    ) AS diff,
    mm.mm_numberOfItems,
    mm.mm_numberOfLines,
    SUM(
        (mm.mm_numberOfItems * mm.mm_numberOfLines) - tl.tl_quantity
    ) 'left'
FROM
    posTransactionLine AS tl
    JOIN posMixMatch AS mm ON tl.tl_itemNumber = mm.mm_item
WHERE
    tl.tl_th_id = ?
    AND(
        CURDATE() BETWEEN mm.mm_activeFrom
        AND mm.mm_activeTo
    )
GROUP BY
    mm.mm_offer,
    mm.mm_offerPrice,
    mm.mm_numberOfItems,
    mm.mm_numberOfLines
HAVING
    SUM(tl.tl_quantity) >= mm.mm_numberOfItems
    AND SUM(tl.tl_quantity) >= mm.mm_numberOfLines
ORDER BY
    SUM(tl.tl_price * tl.tl_quantity) - mm.mm_offerPrice DESC

示例优惠数据 #

[
    {
        "mm_offer": "MM00023",
        "mm_item": "55202206",
        "mm_group": " ",
        "mm_numberOfItems": "1",
        "mm_numberOfLines": "2",
        "mm_activeFrom": "2021-09-01",
        "mm_activeTo": "2023-12-31",
        "mm_description": "Armband 2 för 50:-",
        "mm_offerPrice": "50.00",
        "mm_priority": "0"
    },
    {
        "mm_offer": "MM000231",
        "mm_item": "55202206",
        "mm_group": " ",
        "mm_numberOfItems": "1",
        "mm_numberOfLines": "2",
        "mm_activeFrom": "2021-09-01",
        "mm_activeTo": "2023-12-31",
        "mm_description": "Armband 2 för 10:-",
        "mm_offerPrice": "10.00",
        "mm_priority": "0"
    }
]

问题分析与优化方案 #

当前SQL存在的问题 #

1. 未处理多优惠选最低价逻辑：同一商品的多个有效优惠会全部返回，无法自动筛选出价格最低的优惠（比如示例中MM000231比MM00023更优惠，但当前SQL会同时返回两条）
2. 优惠满足条件逻辑错误：HAVING子句中的SUM(tl.tl_quantity) >= mm.mm_numberOfItems AND SUM(tl.tl_quantity) >= mm.mm_numberOfLines不符合示例中“2件商品享优惠”的规则，正确的条件应该是商品总数≥mm_numberOfItems * mm.mm_numberOfLines（即1*2=2件）
3. 剩余数量计算错误：当前SUM((mm.mm_numberOfItems * mm.mm_numberOfLines) - tl.tl_quantity)的计算逻辑完全错误，无法得到应用优惠后剩余的可适配其他优惠的商品数量

优化后的SQL #

WITH ValidItemOffers AS (
    SELECT 
        mm.mm_item,
        mm.mm_offer,
        mm.mm_offerPrice,
        mm.mm_numberOfItems,
        mm.mm_numberOfLines,
        -- 按商品分组，标记最低价优惠为第1顺位
        ROW_NUMBER() OVER (PARTITION BY mm.mm_item ORDER BY mm.mm_offerPrice ASC) AS offerRank
    FROM posMixMatch AS mm
    WHERE CURDATE() BETWEEN mm.mm_activeFrom AND mm.mm_activeTo
),
BestItemOffers AS (
    -- 只保留每个商品的最低价有效优惠
    SELECT * FROM ValidItemOffers WHERE offerRank = 1
)
SELECT
    GROUP_CONCAT(tl.tl_id) AS lineIds,
    bio.mm_offer,
    SUM(tl.tl_price * tl.tl_quantity) AS originalPrice,
    bio.mm_offerPrice,
    -- 计算应用最优优惠后的总节省金额
    (SUM(tl.tl_price * tl.tl_quantity) - 
     (FLOOR(SUM(tl.tl_quantity)/(bio.mm_numberOfItems * bio.mm_numberOfLines)) * bio.mm_offerPrice)) AS totalSavings,
    bio.mm_numberOfItems,
    bio.mm_numberOfLines,
    -- 计算应用完当前优惠后剩余的可用于其他优惠的商品数量
    SUM(tl.tl_quantity) % (bio.mm_numberOfItems * bio.mm_numberOfLines) AS remainingQuantity
FROM posTransactionLine AS tl
JOIN BestItemOffers AS bio ON tl.tl_itemNumber = bio.mm_item
WHERE tl.tl_th_id = ?
GROUP BY
    bio.mm_offer,
    bio.mm_offerPrice,
    bio.mm_numberOfItems,
    bio.mm_numberOfLines
-- 仅保留商品数量满足优惠要求的记录
HAVING SUM(tl.tl_quantity) >= (bio.mm_numberOfItems * bio.mm_numberOfLines)
ORDER BY totalSavings DESC;

优化说明 #

• 多优惠筛选：通过CTE+ROW_NUMBER()窗口函数，自动为每个商品筛选出当前有效的最低价优惠，满足“选价格最低优惠”的需求
• 修正优惠条件：将HAVING条件调整为商品总数≥优惠所需的总数量（mm_numberOfItems * mm.mm_numberOfLines），符合示例中“2件商品享优惠”的规则
• 剩余数量计算：用取模运算SUM(tl.tl_quantity) % 优惠总数量直接得到应用完最优优惠后剩余的商品数量，支持后续适配其他优惠
• 更直观的优惠价值计算：新增totalSavings字段，展示应用优惠后总共节省的金额，便于业务逻辑使用

备注 #

如果你的Mix and Match规则中mm_numberOfLines指的是不同商品的数量（而非同商品的购买数量），则需要调整分组逻辑，比如检查购物车中是否有至少mm_numberOfLines个不同商品，每个商品至少mm_numberOfItems数量。这种情况需要更复杂的关联和分组，可根据实际规则进一步调整。

内容的提问来源于stack exchange，提问作者Jad Al Saffar