添加CROSS APPLY拆分PAY_PROPERTY后丢失一行数据的排查求助

你遇到的记录丢失问题，核心在于CROSS APPLY是内连接逻辑——只有当REPAY_REF、REPAY_AMOUNT、PAY_PROPERTY三列拆分后的集合存在相同key的元素时，这条组合记录才会被保留。

那条消失的Split Amt=1931.4的记录，大概率是对应的PAY_PROPERTY字段拆分后，没有和另外两列匹配的key：要么是该字段拆分后的元素数量比另外两列少一个，要么是该位置的内容格式异常导致OPENJSON解析失败，最终RR.[key] = PP.[key]的条件不满足，这条记录被过滤掉了。

1. 用OUTER APPLY替代CROSS APPLY保留所有记录 #

把针对PAY_PROPERTY的CROSS APPLY改成OUTER APPLY，同时调整WHERE条件允许PP无匹配key的情况，这样即使PAY_PROPERTY没有对应元素，原记录也会被保留，PP.Value会显示为NULL：

SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        CAST(RIGHT(RR.Value,8) AS DATE) 'Split Date',
        RA.Value 'Split Amt',
        PAY_PROPERTY,
        PP.Value 'PP'

FROM    AA_BILL_DETAILS_Property

        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA
        OUTER APPLY OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP

WHERE   RR.[key] = RA.[key] 
        AND (RR.[key] = PP.[key] OR PP.[key] IS NULL)
ORDER BY    [Split Date] DESC

2. 排查PAY_PROPERTY的原始数据问题 #

找到丢失记录对应的ARRANGEMENT_ID，执行以下SQL单独查看该字段的拆分结果：

SELECT 
    ARRANGEMENT_ID,
    PAY_PROPERTY,
    PP.[key],
    PP.Value
FROM AA_BILL_DETAILS_Property
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP
WHERE ARRANGEMENT_ID = '替换为丢失记录的ARRANGEMENT_ID'
ORDER BY PP.[key]

对比同一条ID下REPAY_REF、REPAY_AMOUNT拆分后的key数量，就能定位出PAY_PROPERTY是否存在分隔符遗漏、内容格式错误等问题。

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