SQL Server中关联支付反向日志对应支付记录的查询需求

核心思路 #

先定位每个Payment_Ref下**第一条后缀为'Reverse'**的支付反向记录，再从同Payment_Ref中找出与该反向记录前缀一致、后缀为'Pay'的历史支付记录。

假设表结构 #

假设你的支付记录表名为PaymentRecords，关键字段包括：

• Payment_Ref：支付参考编号
• Payment_Log：支付日志标识（格式如XXX_Pay、XXX_Reverse）
• CreatedDate：记录生成时间（用于确定首条反向记录的顺序）

完整查询代码 #

-- 第一步：获取每个Payment_Ref的首条Reverse记录并提取前缀
WITH FirstReverseRecords AS (
    SELECT 
        Payment_Ref,
        Payment_Log AS Reverse_Log,
        -- 提取前缀：假设日志格式为「前缀_Reverse」，按下划线分割
        LEFT(Payment_Log, CHARINDEX('_', Payment_Log) - 1) AS Prefix,
        -- 按创建时间排序，取每组第一条Reverse记录
        ROW_NUMBER() OVER (PARTITION BY Payment_Ref ORDER BY CreatedDate ASC) AS rn
    FROM PaymentRecords
    WHERE Payment_Log LIKE '%_Reverse' -- 筛选后缀为Reverse的记录
)
-- 第二步：关联原表，匹配对应前缀的Pay记录
SELECT 
    fr.Payment_Ref,
    fr.Reverse_Log,
    pr.Payment_Log AS Original_Pay_Log
FROM FirstReverseRecords fr
INNER JOIN PaymentRecords pr 
    ON fr.Payment_Ref = pr.Payment_Ref
    AND pr.Payment_Log = CONCAT(fr.Prefix, '_Pay') -- 匹配前缀+_Pay的日志
WHERE fr.rn = 1; -- 仅保留每个Payment_Ref的首条Reverse记录

适配调整说明 #

如果Payment_Log的格式不是用下划线分隔（比如无分隔符，直接以'Reverse'结尾），可以修改前缀提取逻辑：

-- 替换原CTE中的Prefix字段：去掉最后8个字符（'Reverse'共8位）
LEFT(Payment_Log, LEN(Payment_Log) - 8) AS Prefix

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