SQL Server分区中选取无重复最近前置最大日期匹配发票记录

你遇到的是典型的发票与单据一对一不重复匹配场景，可通过递归CTE实现需求，以下是适用SQL Server 2019的查询代码：

-- 同客户下发票按开票日期倒序编号，越新的发票优先级越高
WITH RankedInvoices AS (
    SELECT 
        clientid,
        invoiceid,
        invoicedate,
        ROW_NUMBER() OVER(PARTITION BY clientid ORDER BY invoicedate DESC) AS rn
    FROM Invoice
),
-- 同客户下单据按单据日期倒序编号
RankedDocuments AS (
    SELECT 
        clientid,
        documentid,
        documentdate,
        ROW_NUMBER() OVER(PARTITION BY clientid ORDER BY documentdate DESC) AS rn
    FROM Document
),
-- 递归匹配，确保单据不被重复使用
RecursiveMatch AS (
    -- 先匹配最新的第一张发票
    SELECT 
        i.clientid,
        i.invoiceid,
        i.invoicedate,
        i.rn AS invoice_rn,
        d.documentid,
        d.documentdate,
        d.rn AS last_used_doc_rn
    FROM RankedInvoices i
    OUTER APPLY (
        SELECT TOP 1 documentid, documentdate, rn
        FROM RankedDocuments d
        WHERE d.clientid = i.clientid AND d.documentdate < i.invoicedate
        ORDER BY d.documentdate DESC
    ) d
    WHERE i.rn = 1

    UNION ALL

    -- 按顺序匹配后续发票，仅使用未被占用的更早单据
    SELECT 
        i.clientid,
        i.invoiceid,
        i.invoicedate,
        i.rn AS invoice_rn,
        d.documentid,
        d.documentdate,
        d.rn AS last_used_doc_rn
    FROM RecursiveMatch m
    JOIN RankedInvoices i ON i.clientid = m.clientid AND i.rn = m.invoice_rn + 1
    OUTER APPLY (
        SELECT TOP 1 documentid, documentdate, rn
        FROM RankedDocuments d
        WHERE d.clientid = i.clientid 
            AND d.documentdate < i.invoicedate
            AND d.rn > ISNULL(m.last_used_doc_rn, 0)
        ORDER BY d.documentdate DESC
    ) d
)
SELECT clientid, invoiceid, invoicedate, documentid, documentdate
FROM RecursiveMatch
ORDER BY clientid, invoice_rn

逻辑说明 #

1. 优先匹配开票日期更新的发票，保证最新的发票能先拿到符合条件的最近单据
2. 单据按日期倒序编号后，每次匹配仅使用序号大于上一次已用单据序号的记录，天然避免重复使用
3. 当没有符合条件的单据时，自动返回空值，和你给出的预期输出完全一致

优化建议 #

如果数据量较大，可以创建两个覆盖索引提升查询效率：

• Invoice表创建索引：(clientid, invoicedate DESC) INCLUDE (invoiceid)
• Document表创建索引：(clientid, documentdate DESC) INCLUDE (documentid)

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