如何查找各合同中非最新且付款状态不为Processed的付款记录

需求说明 #

我们需要验证假设「收到新付款时，上一笔付款必须已完成处理（状态为Processed）」，具体要找出所有不属于对应合同最新付款记录且**付款状态不为Processed**的付款记录——这些就是违反假设的反例。

实现思路 #

1. 先针对每个合同（contract_ID），计算其最大的payment_number，也就是该合同的最新付款记录编号；
2. 将原表与这个计算结果关联，筛选出满足以下两个条件的记录：
   • 当前记录的payment_number小于对应合同的最大付款编号（即不是最新记录）；
   • 当前记录的payment_status不等于Processed。

标准SQL查询语句 #

SELECT t.*
FROM payment_records t
JOIN (
    -- 计算每个合同的最新付款编号
    SELECT contract_ID, MAX(CAST(payment_number AS UNSIGNED)) AS max_payment_num
    FROM payment_records
    GROUP BY contract_ID
) max_pay ON t.contract_ID = max_pay.contract_ID
WHERE CAST(t.payment_number AS UNSIGNED) < max_pay.max_payment_num
  AND t.payment_status != 'Processed';

注：因为payment_number是字符类型，所以用CAST转成无符号整数，避免字符串排序导致的错误（比如字符"10"会被认为比"2"小）。

示例数据验证 #

给定示例数据：

执行上述SQL后，得到的反例记录为：

原因：contract2的最新付款编号是3，编号2的记录不是最新状态，但付款状态为Approved（未完成处理），违反了假设；其余非最新记录的状态均为Processed，符合要求。

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