如何在SQL中识别并排除净额为0的交易冲销记录

核心逻辑 #

交易冲销的本质是同一供应商、同一发票ID、同一清算凭证、同一清算日期维度下的多笔金额正负抵消后总净额为0，因此只需要先计算每个维度分组的总金额，再剔除总金额为0的分组下的所有记录即可。

修改后代码 #

select 
    [Vendor ID],
    [Invoice_ID],
    [Clearing Document],
    [Clearing date], 
    [Amount]
from
 (
 select * ,
    -- 计算同分组下所有交易的合计净值，判断是否为完全冲销组
    SUM([Amount]) 
    OVER
         (
         PARTITION BY 
         [Vendor ID],
         [Invoice_ID],
         [Clearing Document],
         [Clearing date]
         ) AS group_net_amount
 from dbo.table
 ) AS filtered_data
-- 仅保留净额不为0的分组下的有效记录
where group_net_amount <> 0

调整点说明 #

• 移除了原PARTITION BY中的Amount字段：原逻辑把金额作为分区条件，会把正负金额分到不同组别，无法计算抵消后的净额
• 用SUM窗口函数替代原COUNT窗口函数：直接统计同一关联交易组的总净额，总净额为0说明该组所有记录是完全冲销的交易，直接全部剔除
• 如果你需要同时保留原需求中的重复付款识别逻辑，可以在子查询中同时保留COUNT窗口函数，在WHERE条件后追加对应过滤规则即可。

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