编写按月份分组计算两表两列总和差值的SQL查询

要实现按月份分组计算TableA和TableB中mass、weight总和的差值，我们可以分两步走：先分别统计两个表的月度汇总数据，再通过全外连接合并并计算差值，这样能覆盖所有有数据的月份（包括只有其中一个表有记录的情况）。

通用SQL查询（适配多数数据库） #

下面的查询用CTE（公共表表达式）拆分逻辑，可读性更强：

WITH MonthlyA AS (
    SELECT
        EXTRACT(YEAR FROM sampleDt) AS Year,
        EXTRACT(MONTH FROM sampleDt) AS Month,
        SUM(mass) AS AMassTotal,
        SUM(weight) AS AWeightTotal
    FROM TableA
    GROUP BY Year, Month
),
MonthlyB AS (
    SELECT
        EXTRACT(YEAR FROM sampleDt) AS Year,
        EXTRACT(MONTH FROM sampleDt) AS Month,
        SUM(mass) AS BMassTotal,
        SUM(weight) AS BWeightTotal
    FROM TableB
    GROUP BY Year, Month
)
SELECT
    COALESCE(a.Year, b.Year) AS Year,
    COALESCE(a.Month, b.Month) AS Month,
    COALESCE(a.AMassTotal, 0) AS AMassTotal,
    COALESCE(a.AWeightTotal, 0) AS AWeightTotal,
    COALESCE(b.BMassTotal, 0) AS BMassTotal,
    COALESCE(b.BWeightTotal, 0) AS BWeightTotal,
    COALESCE(a.AMassTotal, 0) - COALESCE(b.BMassTotal, 0) AS MassDiff,
    COALESCE(a.AWeightTotal, 0) - COALESCE(b.BWeightTotal, 0) AS WeightDiff
FROM MonthlyA a
FULL OUTER JOIN MonthlyB b
    ON a.Year = b.Year AND a.Month = b.Month
ORDER BY Year, Month;

关键细节解释 #

• CTE拆分：MonthlyA和MonthlyB分别计算两个表每个月的mass、weight总和，避免嵌套子查询的混乱。
• COALESCE函数：用来处理某月份只有一个表有数据的情况，把NULL值替换为0，保证差值计算的准确性（比如2017年6月只有TableB的记录，TableA的总和会被视为0）。
• 全外连接（FULL OUTER JOIN）：确保所有在A或B中存在的月份都能被纳入结果，不会遗漏任何有数据的月份。

适配不同数据库的调整 #

如果你的数据库不支持EXTRACT或FULL OUTER JOIN，可以做如下调整：

MySQL #

MySQL不支持FULL OUTER JOIN，可以用UNION ALL结合分组来替代，同时用YEAR()和MONTH()函数提取年月：

WITH MonthlyData AS (
    SELECT
        YEAR(sampleDt) AS Year,
        MONTH(sampleDt) AS Month,
        SUM(mass) AS AMassTotal,
        SUM(weight) AS AWeightTotal,
        0 AS BMassTotal,
        0 AS BWeightTotal
    FROM TableA
    GROUP BY Year, Month
    UNION ALL
    SELECT
        YEAR(sampleDt) AS Year,
        MONTH(sampleDt) AS Month,
        0 AS AMassTotal,
        0 AS AWeightTotal,
        SUM(mass) AS BMassTotal,
        SUM(weight) AS BWeightTotal
    FROM TableB
    GROUP BY Year, Month
)
SELECT
    Year,
    Month,
    SUM(AMassTotal) AS AMassTotal,
    SUM(AWeightTotal) AS AWeightTotal,
    SUM(BMassTotal) AS BMassTotal,
    SUM(BWeightTotal) AS BWeightTotal,
    SUM(AMassTotal) - SUM(BMassTotal) AS MassDiff,
    SUM(AWeightTotal) - SUM(BWeightTotal) AS WeightDiff
FROM MonthlyData
GROUP BY Year, Month
ORDER BY Year, Month;

SQL Server #

用DATEPART函数替代EXTRACT：

WITH MonthlyA AS (
    SELECT
        DATEPART(YEAR, sampleDt) AS Year,
        DATEPART(MONTH, sampleDt) AS Month,
        SUM(mass) AS AMassTotal,
        SUM(weight) AS AWeightTotal
    FROM TableA
    GROUP BY DATEPART(YEAR, sampleDt), DATEPART(MONTH, sampleDt)
),
MonthlyB AS (
    SELECT
        DATEPART(YEAR, sampleDt) AS Year,
        DATEPART(MONTH, sampleDt) AS Month,
        SUM(mass) AS BMassTotal,
        SUM(weight) AS BWeightTotal
    FROM TableB
    GROUP BY DATEPART(YEAR, sampleDt), DATEPART(MONTH, sampleDt)
)
SELECT
    COALESCE(a.Year, b.Year) AS Year,
    COALESCE(a.Month, b.Month) AS Month,
    COALESCE(a.AMassTotal, 0) AS AMassTotal,
    COALESCE(a.AWeightTotal, 0) AS AWeightTotal,
    COALESCE(b.BMassTotal, 0) AS BMassTotal,
    COALESCE(b.BWeightTotal, 0) AS BWeightTotal,
    COALESCE(a.AMassTotal, 0) - COALESCE(b.BMassTotal, 0) AS MassDiff,
    COALESCE(a.AWeightTotal, 0) - COALESCE(b.BWeightTotal, 0) AS WeightDiff
FROM MonthlyA a
FULL OUTER JOIN MonthlyB b
    ON a.Year = b.Year AND a.Month = b.Month
ORDER BY Year, Month;

样本数据的期望输出 #

根据你提供的样本数据，执行查询后会得到如下结果：

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