SQL Server中Transaction表借贷不平衡查询及重复交易识别

针对你的需求，我来一步步拆解解决方案，兼顾百万级数据的性能和重复交易的处理：

重复交易会干扰借贷平衡的判断，得先识别并清理它们。

识别重复交易 #

下面的查询可以找出所有重复的交易（按交易日期、账户、借贷金额分组），并统计重复次数：

SELECT postDate, account, debit, credit, COUNT(*) AS duplicate_count
FROM Transaction
GROUP BY postDate, account, debit, credit
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

生成去重后的数据集 #

如果要直接得到无重复的交易数据（保留每条唯一交易的最早记录），可以用ROW_NUMBER()生成编号，后续所有不平衡查询都基于这个数据集：

WITH DeduplicatedTransactions AS (
    SELECT 
        id,
        postDate,
        account,
        debit,
        credit,
        -- 按交易核心维度分组，给重复记录编号，只保留第一条
        ROW_NUMBER() OVER (PARTITION BY postDate, account, debit, credit ORDER BY id) AS rn
    FROM Transaction
)
SELECT * FROM DeduplicatedTransactions WHERE rn = 1;

Union适合把借贷交易标准化后，从整体维度找出哪些日期、金额的交易存在不平衡。核心思路是把借方记为负金额、贷方记为正金额，分组后如果净余额不为0，说明该金额在对应日期下没有完全匹配的借贷记录：

WITH StandardizedTransactions AS (
    SELECT 
        id,
        postDate,
        account,
        -- 标准化金额：借方支出为负，贷方收入为正
        amount = CASE WHEN debit > 0 THEN -debit ELSE credit END,
        transaction_type = CASE WHEN debit > 0 THEN 'DEBIT' ELSE 'CREDIT' END
    FROM Transaction
    WHERE debit > 0 OR credit > 0 -- 过滤无金额的无效记录
),
DeduplicatedTransactions AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY postDate, account, amount ORDER BY id) AS rn
    FROM StandardizedTransactions
)
SELECT 
    postDate,
    ABS(amount) AS transaction_amount,
    SUM(CASE WHEN transaction_type = 'DEBIT' THEN 1 ELSE 0 END) AS total_debits,
    SUM(CASE WHEN transaction_type = 'CREDIT' THEN 1 ELSE 0 END) AS total_credits,
    SUM(amount) AS net_balance -- 借贷平衡时该值应为0
FROM DeduplicatedTransactions
WHERE rn = 1
GROUP BY postDate, ABS(amount)
HAVING SUM(amount) <> 0 -- 筛选出不平衡的记录
ORDER BY postDate, transaction_amount;

如果需要找到具体哪笔贷方记录没有对应借方，用EXISTS关联会更直接。这个查询会找出所有去重后的贷方记录，且不存在同日期、同金额、不同账户的借方记录：

WITH DeduplicatedTransactions AS (
    SELECT 
        id,
        postDate,
        account,
        debit,
        credit,
        ROW_NUMBER() OVER (PARTITION BY postDate, account, debit, credit ORDER BY id) AS rn
    FROM Transaction
)
-- 找出无对应借方的贷方记录
SELECT 
    c.id AS credit_transaction_id,
    c.postDate,
    c.account AS credit_account,
    c.credit AS unmatched_amount
FROM DeduplicatedTransactions c
WHERE c.credit > 0 
  AND c.rn = 1
  AND NOT EXISTS (
      SELECT 1
      FROM DeduplicatedTransactions d
      WHERE d.rn = 1
        AND d.postDate = c.postDate
        AND d.debit = c.credit
        AND d.account <> c.account -- 借方账户需与贷方账户不同
  )
-- 可选：加上找出无对应贷方的借方记录
UNION ALL
SELECT 
    d.id AS debit_transaction_id,
    d.postDate,
    d.account AS debit_account,
    d.debit AS unmatched_amount
FROM DeduplicatedTransactions d
WHERE d.debit > 0 
  AND d.rn = 1
  AND NOT EXISTS (
      SELECT 1
      FROM DeduplicatedTransactions c
      WHERE c.rn = 1
        AND c.postDate = d.postDate
        AND c.credit = d.debit
        AND c.account <> d.account
  )
ORDER BY postDate, unmatched_amount;

因为表有百万级数据，必须通过索引来加速查询：

• 给Transaction表创建非聚集索引：CREATE NONCLUSTERED INDEX IX_Transaction_PostDate_Account_Debit_Credit ON Transaction(postDate, account, debit, credit) INCLUDE(id);，这个索引能大幅加速去重的ROW_NUMBER()计算和分组查询。
• 如果用EXISTS方案，额外创建索引CREATE NONCLUSTERED INDEX IX_Transaction_PostDate_Debit ON Transaction(postDate, debit) INCLUDE(account);和IX_Transaction_PostDate_Credit ON Transaction(postDate, credit) INCLUDE(account);，能快速匹配对应日期和金额的借贷记录。

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