基于到期优先级的MySQL现金返还部分金额兑换实现问询

要实现按到期时间从早到晚的顺序抵扣指定金额（示例中为35美元），可以借助MySQL窗口函数计算累计金额，再通过逻辑判断每条记录的实际抵扣金额。以下是具体实现方案：

核心思路 #

1. 先筛选并排序符合条件的现金返还记录（和原查询逻辑一致）；
2. 计算每条记录及之前所有记录的累计金额，确保抵扣顺序正确；
3. 根据累计金额与目标抵扣金额的对比，计算每条记录的实际抵扣额：
   • 若累计金额≤目标金额：全额抵扣当前记录的金额；
   • 若累计金额>目标金额，但之前的累计金额<目标金额：抵扣剩余的差额；
   • 其余情况不产生抵扣，最终过滤掉这些无抵扣的记录。

完整SQL语句 #

SELECT 
    id,
    amount AS `金额（amount）`,
    point_expiry AS `到期时间（point_expiry）`,
    used AS `是否已使用（used）`,
    CASE
        WHEN running_total <= 35 THEN amount
        WHEN running_total - amount < 35 THEN 35 - (running_total - amount)
        ELSE 0
    END AS `已抵扣金额（used_amount）`
FROM (
    SELECT 
        *,
        SUM(amount) OVER (
            ORDER BY 
                CASE WHEN point_expiry = '0000-00-00 00:00:00' THEN 9999 ELSE 1 END,
                point_expiry
        ) AS running_total
    FROM cashback
    WHERE user_id = 1 
      AND used = 'NO' 
      AND (point_expiry = '0000-00-00 00:00:00' OR point_expiry >= NOW())
) AS sorted_cashback
WHERE `已抵扣金额（used_amount）` > 0
ORDER BY 
    CASE WHEN point_expiry = '0000-00-00 00:00:00' THEN 9999 ELSE 1 END,
    point_expiry;

逻辑解释 #

• 内层子查询：在原筛选条件的基础上，使用SUM(amount) OVER (...)窗口函数计算累计金额running_total，排序规则和原查询一致，保证先抵扣到期早的记录，永不过期的（0000-00-00 00:00:00）最后处理。
• 外层查询：通过CASE语句计算实际抵扣额：
  • 对于ID=3的记录，累计金额为30≤35，所以抵扣全额30；
  • 对于ID=1的记录，累计金额为50>35，但之前的累计（50-20=30）<35，所以抵扣35-30=5；
  • 对于ID=2的记录，累计金额为100>35，且之前的累计（100-50=50）>35，所以抵扣0，最终被过滤。
• 过滤与排序：最后只保留有抵扣金额的记录，并按原规则排序，得到预期结果。

简化原查询条件 #

原查询中的IF(CONCAT(point_expiry)!='0000-00-00 00:00:00', point_expiry >= NOW(), NOW())可以简化为(point_expiry = '0000-00-00 00:00:00' OR point_expiry >= NOW())，逻辑完全一致，更易读。

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