如何计算两个表分组求和的差值？SQL查询结果异常求助

直接将两个表通过product关联后再求和，会产生笛卡尔积，导致求和结果被重复计算。比如product=a在Table1有2条记录，Table2有2条，关联后会生成4条记录，此时sum(table1.quantity)会变成(7+3)*2=20，sum(table2.quantity)变成(2+2)*2=8，差值完全偏离预期。

先分别对两个表按product分组求和，再关联计算差值，避免笛卡尔积的影响：

SELECT 
    COALESCE(t1.product, t2.product) AS product,
    COALESCE(t1.total_quantity, 0) - COALESCE(t2.total_quantity, 0) AS difference
FROM (
    -- 计算Table1每个product的总数量
    SELECT product, SUM(quantity) AS total_quantity
    FROM Table1
    GROUP BY product
) t1
-- 全连接确保包含只在其中一个表存在的product
FULL JOIN (
    -- 计算Table2每个product的总数量
    SELECT product, SUM(quantity) AS total_quantity
    FROM Table2
    GROUP BY product
) t2 ON t1.product = t2.product;

补充说明 #

• 子查询预先分组求和后，每个product仅保留一条总数量记录，关联时不会产生重复计算。
• COALESCE函数用于处理某表中不存在对应product的情况，将null值替换为0，保证差值计算正常。
• 如果只需要两个表都存在的product，可以将FULL JOIN替换为INNER JOIN。

执行上述查询后，会得到期望的结果：

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