SQL Server跨表按月汇总金额差异排查及Unknown标识需求

我完全get到你的需求啦——要把两个存储相似数据的表按月汇总金额，一旦当月两表的总金额对不上，就把差额以Person = 'Unknown'的形式单独列出来。下面是具体的实现方案，咱们一步步来看：

核心思路拆解 #

• 先做月度汇总：分别对两个表按月份分组，算出每个月的总金额，得到清晰的月度汇总数据，方便后续对比。
• 关联两个汇总结果：用FULL JOIN按月份关联两个汇总表，确保不管哪个表有该月份的数据，都不会被漏掉。
• 识别差异并生成Unknown记录：判断两个表的月度总金额是否相等，若不等就计算差额，生成对应Person = 'Unknown'的行；如果需要保留原表的明细，也可以把明细和差异行合并起来。

具体SQL代码实现 #

WITH Table1Monthly AS (
    SELECT 
        -- 格式化日期为Jun-17这类形式，指定英文环境避免格式偏差
        FORMAT(TransactionDate, 'MMM-yy', 'en-GB') AS MonthYear,
        SUM(Amount) AS TotalAmount
    FROM Table1
    GROUP BY FORMAT(TransactionDate, 'MMM-yy', 'en-GB')
),
Table2Monthly AS (
    SELECT 
        FORMAT(TransactionDate, 'MMM-yy', 'en-GB') AS MonthYear,
        SUM(Amount) AS TotalAmount
    FROM Table2
    GROUP BY FORMAT(TransactionDate, 'MMM-yy', 'en-GB')
)
-- 生成差异行：总金额不等的月份，输出Unknown记录
SELECT 
    COALESCE(t1.MonthYear, t2.MonthYear) AS MonthYear,
    'Unknown' AS Person,
    -- 计算两表总金额的差值，用COALESCE把NULL转为0避免计算错误
    ABS(COALESCE(t1.TotalAmount, 0) - COALESCE(t2.TotalAmount, 0)) AS Amount
FROM Table1Monthly t1
FULL JOIN Table2Monthly t2 
    ON t1.MonthYear = t2.MonthYear
WHERE COALESCE(t1.TotalAmount, 0) <> COALESCE(t2.TotalAmount, 0)

-- 如果需要保留原表的所有明细记录，就加上下面的UNION ALL部分
UNION ALL
SELECT 
    FORMAT(TransactionDate, 'MMM-yy', 'en-GB') AS MonthYear,
    Person,
    Amount
FROM Table1
UNION ALL
SELECT 
    FORMAT(TransactionDate, 'MMM-yy', 'en-GB') AS MonthYear,
    Person,
    Amount
FROM Table2;

代码细节说明 #

• 日期格式化：用FORMAT函数指定en-GB语言环境，确保月份缩写是英文（比如Jun），避免因为服务器语言设置不同导致格式混乱。
• 处理NULL值：用COALESCE把某个月份缺失的总金额转为0，这样计算差值时不会出现NULL结果。
• 差异筛选：WHERE子句精准找出两表总金额不相等的月份，只针对这些月份生成Unknown记录。
• 明细合并（可选）：如果需要同时展示原表的所有明细和差异行，就保留后面的UNION ALL部分；如果只需要差异记录，直接删掉这部分即可。

比如你提到的Jun-17例子，表1总金额£75，表2总金额£125，最终会生成一条差异记录：

这样就完美满足你的需求啦～

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