T-SQL查询求和时出现重复计数问题求助

我需要编写一个T-SQL查询来将两列数值相加，但当前代码执行后出现了重复计数的问题。已查找类似案例但未找到匹配方案，以下是我的查询代码：

SELECT DISTINCT t.Reference, 
u.FileID, 
u.UnpaidVAT, 
u.UnpaidFees, 
CAST(
SUM(u.UnpaidVAT+u.UnpaidFees) 
AS 
DECIMAL(8,2)) AS TotalOutstanding
FROM UnpaidFees u
LEFT JOIN Trans T ON u.Reference=t.InvoiceID
INNER JOIN Matters m ON u.FileId=m.FileId
WHERE t.Narration LIKE 'Invoice'
AND (u.UnpaidVAT>0 OR u.UnpaidFees>0)
AND m.Status=1
GROUP BY t.Reference, u.matterID, u.UnpaidVAT,u.UnpaidFees
;

当前查询结果如下，可见UnpaidVAT和UnpaidFees被重复计数：

另外，Trans表的t.InvoiceID、t.Narration、t.Reference字段，以及UnpaidFees表的u.FileID字段在各自表中存在重复条目。以下是各表的示例数据：

Trans表示例数据 #

UnpaidFees表示例数据 #

Matters表示例数据 #

请问我的代码哪里出错了？

错误原因 #

1. 连接产生重复行：Trans表中同一个InvoiceID对应多条记录（比如InvoiceID=1有两条），UnpaidFees和Trans关联时，每条UnpaidFees记录会被重复匹配，SUM计算时会把同一组值累加多次，导致结果翻倍（比如第一条记录20+100=120，最终结果240正好是两倍）。
2. GROUP BY与DISTINCT逻辑冗余错误：GROUP BY已经做了分组，再用DISTINCT完全多余；且GROUP BY包含了u.UnpaidVAT和u.UnpaidFees这两个明细值，分组后SUM实际是对单条记录求和，加上连接产生的重复行，就会重复累加同一值。

修正后的代码 #

方案一：先对Trans表去重再关联（适用于UnpaidFees无重复明细的场景）

SELECT 
    t.Reference, 
    u.FileID, 
    u.UnpaidVAT, 
    u.UnpaidFees, 
    CAST(u.UnpaidVAT + u.UnpaidFees AS DECIMAL(8,2)) AS TotalOutstanding
FROM UnpaidFees u
INNER JOIN Matters m ON u.FileId = m.FileId
-- 先获取每个InvoiceID唯一的Invoice记录
INNER JOIN (
    SELECT DISTINCT InvoiceID, Reference
    FROM Trans
    WHERE Narration LIKE 'Invoice'
) t ON u.Reference = t.InvoiceID
WHERE (u.UnpaidVAT > 0 OR u.UnpaidFees > 0)
AND m.Status = 1;

方案二：先聚合UnpaidFees再关联（适用于UnpaidFees存在重复明细的场景）

如果UnpaidFees中同一FileID/Reference有多条记录需要求和，用此方案：

SELECT 
    t.Reference, 
    agg.FileID, 
    agg.TotalUnpaidVAT, 
    agg.TotalUnpaidFees, 
    CAST(agg.TotalUnpaidVAT + agg.TotalUnpaidFees AS DECIMAL(8,2)) AS TotalOutstanding
FROM (
    SELECT 
        FileID,
        Reference,
        SUM(UnpaidVAT) AS TotalUnpaidVAT,
        SUM(UnpaidFees) AS TotalUnpaidFees
    FROM UnpaidFees
    WHERE (UnpaidVAT > 0 OR UnpaidFees > 0)
    GROUP BY FileID, Reference
) agg
INNER JOIN Matters m ON agg.FileId = m.FileId
INNER JOIN (
    SELECT DISTINCT InvoiceID, Reference
    FROM Trans
    WHERE Narration LIKE 'Invoice'
) t ON agg.Reference = t.InvoiceID
WHERE m.Status = 1;

说明 #

• 方案一通过去重Trans表的重复记录，避免了关联时产生重复行，直接计算单条记录的VAT+Fees即可得到正确结果。
• 方案二先在UnpaidFees内部完成分组求和，再关联其他表，从根源上避免了重复累加的问题。

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