SQL Server 为什么CROSS APPLY会导致少一行？

我拆分了一些用特殊字符分隔的值（REPAY_AMOUNT和REPAY_REF），并按如下方式交叉应用它们：

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

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

WHERE   RR.[key] = RA.[key]
ORDER BY    [Split Date] DESC

结果如下：
| 排列ID|偿还金额|偿还参考|拆分日期|拆分金额|
| - ------|- ------|- ------|- ------|- ------|
| AA21084T517V型号|三千六百七十八点四一至二千零五〇点二五|AAACT223212XPWZDCY-20221117和AAACT222933SXX4KLB-20221020的相关规定|二〇二二年十一月十七日|小行星3678.41|
| AA21084T517V型号|三千六百七十八点四一至二千零五〇点二五|AAACT223212XPWZDCY-20221117和AAACT222933SXX4KLB-20221020的相关规定|二〇二二年十月二十日|小行星2050. 25|
| AA21084T517V型号|一九三一年四月至一九三一年四月|AAACT22059QK5XTXSD-悬挂装置-20220228和AAACT22059QK5XTXSD-20220228|2022年2月28日|一九三一年四月|
| AA21084T517V型号|一九三一年四月至一九三一年四月|AAACT22059QK5XTXSD-悬挂装置-20220228和AAACT22059QK5XTXSD-20220228|2022年2月28日|一九三一年四月|
| AA21084T517V型号|零|零|一九○ ○年一月一日||
| AA21084T517V型号|零|零|一九○ ○年一月一日||

- 问题：**

现在我想拆分并交叉应用另一列（PAY_PROPERTY），如下所示：

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
        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP

WHERE   RR.[key] = RA.[key] AND RR.[key] = PP.[key]
ORDER BY    [Split Date] DESC

但这样做会丢失一行（即其中一行具有Split Amt = 1931.4），结果为：
| 排列ID|偿还金额|偿还参考|拆分日期|拆分金额|薪资_房产|聚丙烯|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|
| AA21084T517V型号|三千六百七十八点四一至二千零五〇点二五|AAACT223212XPWZDCY-20221117和AAACT222933SXX4KLB-20221020的相关规定|二〇二二年十一月十七日|小行星3678.41|账户？本金|账户|
| AA21084T517V型号|三千六百七十八点四一至二千零五〇点二五|AAACT223212XPWZDCY-20221117和AAACT222933SXX4KLB-20221020的相关规定|二〇二二年十月二十日|小行星2050. 25|账户？本金|委托人|
| AA21084T517V型号|一九三一年四月至一九三一年四月|AAACT22059QK5XTXSD-悬挂装置-20220228和AAACT22059QK5XTXSD-20220228|2022年2月28日|一九三一年四月|零||
| AA21084T517V型号|零|零|一九○ ○年一月一日||零||
| AA21084T517V型号|零|零|一九○ ○年一月一日||零||
有人能帮忙吗？

您的问题是每列中的拆分值数量不等，因此联接条件失败。您不能将ON子句与APPLY一起使用，因此可以使用OUTER APPLY（其工作原理与LEFT JOIN类似），并将WHERE条件移到APPLY中。

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 (
    SELECT *
    FROM OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP
    WHERE RR.[key] = PP.[key]
) PP
WHERE   RR.[key] = RA.[key]
ORDER BY
    [Split Date] DESC

如果其他列也有可变数量的拆分，则需要完全联接：

SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        PAY_PROPERTY,
        v.*
FROM    AA_BILL_DETAILS_Property
OUTER APPLY (
    SELECT
        CAST(RIGHT(RR.Value,8) AS DATE) [Split Date],
        RA.Value [Split Amt],
        PP.Value PP
    FROM OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
    FULL JOIN OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA
        ON RR.[key] = RA.[key]
    FULL JOIN OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP
        ON ISNULL(RA.[key], RR.[key]) = PP.[key]
) v
ORDER BY
    [Split Date] DESC

注意第二个连接条件中的ISNULL。
db<>fiddle

SQL Server 为什么CROSS APPLY会导致少一行？

1条答案

相关问题

热门标签

最新问答