如何查找相对前序记录状态顺序异常的合同付款记录

基础信息说明 #

表结构 #

业务表为合同付款记录表，所有合同按月生成付款记录，字段如下：

• ID：创建付款记录时自动分配的唯一标识
• payment_number：数字组成的字符串，取值随付款月份递增，例如"1"、"2"、"3"
• payment_status：付款状态，可选枚举值为Received、Rejected、Approved、Processed
• contract_ID：字母+数字组成的合同唯一标识字符串

业务规则 #

任意一笔付款的状态进度不得超前于同合同下前一笔付款的状态，例如前一笔付款状态为Received时，当前付款状态仅允许为Received。结合流程要求，同合同下付款状态需按期数同步流转，不得出现相邻期数状态不一致的情况，否则判定为异常。
需求：查找所有违反规则的异常记录，支持按合同分组定位异常，可同时查看异常记录及对应前序付款的状态信息。

样例输入数据 #

ID   contract_ID  payment_number payment_status
1    contract1      1           Processed
2    contract1      2           Received
3    contract2      1          Processed
4    contract2      2           Approved
5    contract1      3           Approved
6    contract2      3           Processed

期望输出 #

按合同分组返回异常记录：

1. contract1 异常记录

ID   contract_ID  payment_number payment_status
2    contract1      2           Received
5    contract1      3           Approved

2. contract2 异常记录

ID   contract_ID  payment_number payment_status
4    contract2      2           Approved
6    contract2      3        Processed

实现方案 #

核心逻辑 #

1. 按contract_ID对所有付款记录分组，组内将payment_number转为数值后按升序排序，对齐付款的先后顺序。
2. 用窗口函数取每笔记录紧邻的上一期付款的状态值，排除每个合同的第一笔付款（无前置付款记录，无异常可能）。
3. 比对当前记录状态和上一期付款状态，两者不一致即为异常记录（既包含状态超前的场景，也包含状态滞后漏更的场景，完全匹配样例判定逻辑）。
4. 最终结果可按contract_ID分组返回，方便业务人员按合同排查异常。

SQL实现（支持MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

WITH payment_with_prev_status AS (
    SELECT
        *,
        -- 取同合同上一期付款的状态
        LAG(payment_status, 1) OVER (
            PARTITION BY contract_ID 
            ORDER BY CAST(payment_number AS UNSIGNED)
        ) AS prev_payment_status
    FROM contract_payment_record -- 替换为实际业务表名
)
-- 筛选异常记录：存在上一期状态，且和当前状态不一致
SELECT
    ID,
    contract_ID,
    payment_number,
    payment_status
FROM payment_with_prev_status
WHERE prev_payment_status IS NOT NULL
  AND payment_status != prev_payment_status
-- 按合同、期数排序，方便分组查看
ORDER BY contract_ID, CAST(payment_number AS UNSIGNED);

逻辑验证 #

将样例数据代入上述SQL运行，返回结果和期望输出完全一致：

• contract1下，ID2（第2期，状态Received）上一期状态为Processed，不一致 → 异常；ID5（第3期，状态Approved）上一期状态为Received，不一致 → 异常
• contract2下，ID4（第2期，状态Approved）上一期状态为Processed，不一致 → 异常；ID6（第3期，状态Processed）上一期状态为Approved，不一致 → 异常
  如果需要查看异常记录对应的前序付款状态，只需要调整最终SELECT的字段，把prev_payment_status查出来即可，不需要额外关联查询。

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