SQL Server按规则筛选相同发票号对应最早过账日期行的实现方法

实现思路 #

使用窗口函数给同发票号的数据打标记，无需嵌套子查询或关联，单条查询即可满足全部规则：

• 用ROW_NUMBER()对同发票号的数据按过账日期升序排序，最早过账行的序号标记为1
• 用COUNT()统计同发票号的总行数，判断是否为单过账日期场景
• 用MAX()取同发票号的发票日期做新旧发票分界判定

实现代码 #

-- 替换为业务约定的新旧发票分界日期
DECLARE @CutoffDate DATE = '202X-XX-XX';

WITH MarkedInvoice AS (
    SELECT
        -- 低版本SQL Server请直接替换为实际需要返回的业务字段，不要写*
        *,
        ROW_NUMBER() OVER (PARTITION BY [INVOICE NUMBER] ORDER BY [Posting date] ASC) AS post_date_rn,
        COUNT(1) OVER (PARTITION BY [INVOICE NUMBER]) AS total_post_count,
        MAX([Invoice date]) OVER (PARTITION BY [INVOICE NUMBER]) AS inv_date
    FROM 你的业务表名 -- 替换为实际存储发票数据的表名
)
SELECT
    -- 此处列示所有需要返回的业务字段，排除下方三个临时标记字段即可
    [INVOICE NUMBER],
    [Posting date],
    [Invoice date]
    -- 其他业务字段按需补充
FROM MarkedInvoice
WHERE
    -- 单过账日期的发票直接保留
    total_post_count = 1
    -- 旧发票全量保留
    OR inv_date <= @CutoffDate
    -- 新发票仅保留最早过账的行
    OR (inv_date > @CutoffDate AND post_date_rn = 1)

适配说明 #

• 代码默认同发票号的发票日期一致，符合常规业务逻辑，若业务允许同发票号对应多个发票日期，可调整PARTITION BY的分组规则适配
• 若需要叠加「库中已存在该发票则全量保留」的逻辑，可在CTE中新增关联历史发票表的逻辑，或修改inv_date的判定条件为对应存在性判断即可

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