SQL Server 2016中如何编写查询标记付款后缺失的LAReserve跟进记录

需求背景 #

需要识别交易数据中完成付款操作后，未按规则生成LAReserve = 0跟进记录的异常案件。系统配置要求必须手动录入0值准备金记录，无法通过抵扣逻辑自动生成，无权限修改该配置。

测试数据样例 #

SELECT 123456 AS Casekey
        , 1 as rnk
        , NULL AS LAPayment
        , 1080.00 AS LAReserve
UNION ALL
SELECT 123456 AS Casekey
        , 2 as rnk
        , 1080.00 AS LAPayment
        , null AS LAReserve
UNION ALL
SELECT 123456 AS Casekey
        , 3 as rnk
        , NULL AS LAPayment
        , NULL AS LAReserve

业务判断规则 #

• 同一Casekey下交易按rnk字段升序排序，代表交易发生顺序
• 当某条交易出现非空LAPayment付款值后，后续所有交易中必须存在至少一条LAReserve = 0的记录
• 若付款后不存在该类记录，判定为异常场景

实现查询代码 #

WITH CasePaymentInfo AS (
    -- 提取每个案件的付款交易序号，以及案件下最大交易序号
    SELECT 
        Casekey,
        MAX(CASE WHEN LAPayment IS NOT NULL THEN rnk ELSE 0 END) AS payment_rnk,
        MAX(rnk) AS max_tran_rnk
    FROM 你的交易表名 -- 替换为实际表名
    GROUP BY Casekey
    -- 过滤掉没有付款记录的案件
    HAVING MAX(CASE WHEN LAPayment IS NOT NULL THEN rnk ELSE 0 END) > 0
),
CaseFollowReserve AS (
    -- 判断付款后是否存在0值准备金记录
    SELECT 
        c.Casekey,
        CASE WHEN EXISTS(
            SELECT 1 FROM 你的交易表名 t
            WHERE t.Casekey = c.Casekey
                AND t.rnk > c.payment_rnk
                AND t.LAReserve = 0
        ) THEN 0 ELSE 1 END AS is_abnormal
    FROM CasePaymentInfo c
    -- 过滤掉付款是最后一条交易、还没到后续录入节点的案件，避免误判
    WHERE c.payment_rnk < c.max_tran_rnk
)
-- 输出所有异常案件号
SELECT Casekey FROM CaseFollowReserve WHERE is_abnormal = 1

查询说明 #

• 代码中你的交易表名需要替换为实际存储交易数据的表名
• 输出结果为所有符合异常规则的案件Casekey列表
• 兼容SQL Server 2016及以上版本的语法要求

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