SQL Query 如何让 Payments 与 Invoices 仅匹配一次

2026-10-24支付阿杰

结论

先在每个 Vendor + Amount 分组内，分别用 ROW_NUMBER() 为发票和付款编号，再按编号进行 FULL OUTER JOIN。

同一金额的第 1 张发票会匹配第 1 笔付款，第 2 张发票会匹配第 2 笔付款。如果某一侧的记录较少，多出的记录会保留为未匹配记录，不需要使用 cursor。

SQL Server 示例

WITH RankedInvoices AS
(
    SELECT
        Vendor,
        [Invoice No],
        Amount,
        ROW_NUMBER() OVER
        (
            PARTITION BY Vendor, Amount
            ORDER BY [Invoice No]
        ) AS MatchNo
    FROM Invoices
),
RankedPayments AS
(
    SELECT
        Vendor,
        [Payment No],
        Amount,
        ROW_NUMBER() OVER
        (
            PARTITION BY Vendor, Amount
            ORDER BY [Payment No]
        ) AS MatchNo
    FROM Payments
),
MatchedRows AS
(
    SELECT
        I.Vendor AS InvoiceVendor,
        I.[Invoice No] AS InvoiceNo,
        I.Amount AS InvoiceAmount,

        P.Vendor AS PaymentVendor,
        P.[Payment No] AS PaymentNo,
        P.Amount AS PaymentAmount,

        COALESCE(I.Vendor, P.Vendor) AS SortVendor
    FROM RankedInvoices AS I
    FULL OUTER JOIN RankedPayments AS P
        ON  P.Vendor = I.Vendor
        AND P.Amount = I.Amount
        AND P.MatchNo = I.MatchNo
)
SELECT
    ROW_NUMBER() OVER
    (
        ORDER BY
            SortVendor,
            CASE WHEN InvoiceNo IS NULL THEN 1 ELSE 0 END,
            InvoiceNo,
            PaymentNo
    ) AS Seq,
    InvoiceVendor AS Vendor,
    InvoiceNo AS [Invoice No],
    InvoiceAmount AS [Invoice Amount],
    PaymentVendor AS Vendor,
    PaymentNo AS [Payment No],
    PaymentAmount AS [Payment Amount]
FROM MatchedRows
ORDER BY Seq;

为什么能保证一对一

以 V1、金额 1000 为例，编号结果如下：

Invoice A2 -> MatchNo = 1

Payment P1 -> MatchNo = 1
Payment P2 -> MatchNo = 2

连接时除了比较 Vendor 和 Amount，还要求 MatchNo 相同：

P.Vendor  = I.Vendor
AND P.Amount   = I.Amount
AND P.MatchNo  = I.MatchNo

因此，A2 只能与 P1 匹配。由于没有第 2 张金额为 1000 的发票，P2 会作为未匹配付款保留下来。

再看另一个例子。V1 有两张金额为 1500 的发票，但只有一笔金额为 1500 的付款：

Invoice A3 -> MatchNo = 1
Invoice A5 -> MatchNo = 2

Payment P3 -> MatchNo = 1

最终，A3 与 P3 匹配，A5 保持未匹配。

`FULL OUTER JOIN` 的作用

如果使用普通的 INNER JOIN，未匹配的数据会被过滤；如果使用 LEFT JOIN，则只能完整保留发票一侧的数据。

FULL OUTER JOIN 可以同时保留：

已匹配的发票和付款；
没有对应付款的发票；
没有对应发票的付款。

外层排序中的以下表达式：

CASE WHEN InvoiceNo IS NULL THEN 1 ELSE 0 END

会让每个 Vendor 的发票记录排在前面，仅存在于付款表中的记录排在后面。

注意事项

排序字段必须能确定“第一条”

ROW_NUMBER() 中的 ORDER BY 决定记录的匹配顺序。示例使用：

ORDER BY [Invoice No]
ORDER BY [Payment No]

如果编号可能重复，或者 A10、A2 这类文本的排序结果不符合业务顺序，应改用稳定且唯一的字段，例如主键，或创建时间与主键的组合：

ORDER BY CreatedAt, InvoiceId

ORDER BY CreatedAt, PaymentId

否则，多次执行时可能得到不同的匹配结果。

金额字段应使用精确数值类型

财务金额通常应使用 decimal 或 numeric，不建议使用 float。浮点数存在精度误差，看起来相同的金额可能无法满足以下条件：

P.Amount = I.Amount

`NULL` 金额默认不会互相匹配

在 SQL 中，NULL = NULL 不成立，因此金额为 NULL 的发票和付款都会保持未匹配。如果业务要求两个 NULL 金额也能配对，需要显式修改连接条件：

AND
(
    P.Amount = I.Amount
    OR (P.Amount IS NULL AND I.Amount IS NULL)
)

这套方法实现的是“同 Vendor、同金额，并按指定顺序依次一对一配对”，不能据此认定付款与发票之间存在真实的业务核销关系。如果表中有明确的付款申请号、发票引用号或核销明细，应优先使用这些业务标识进行关联。