SQL关联多表结合SUM函数计算人均费用结果异常求助

问题根源 #

直接对三个表做全连接会产生笛卡尔积：如果某个人在test1有N条消费记录，在test2有M条，连接后会生成N×M条记录，SUM时每条test1的金额会被重复计算M次，test2的金额被重复计算N次，导致结果被放大，出现类似"相乘"的错误。

解决方案 #

先分别计算每个超市的个人费用总和，再通过桥接表关联两个结果集，避免笛卡尔积：

-- 先计算超市1的个人费用总和
WITH grocery1_total AS (
    SELECT j.Name1, SUM(t1.Amount) AS Grocery1
    FROM test1 t1
    FULL JOIN junction1 j ON j.ID1 = t1.ID1
    GROUP BY j.Name1
),
-- 再计算超市2的个人费用总和
grocery2_total AS (
    SELECT j.Name1, SUM(t2.Amount) AS Grocery2
    FROM test2 t2
    FULL JOIN junction1 j ON j.ID2 = t2.ID2
    GROUP BY j.Name1
)
-- 关联两个结果集，计算总费用并排序
SELECT 
    COALESCE(g1.Name1, g2.Name1) AS Name,
    COALESCE(g1.Grocery1, 0) AS Grocery1,
    COALESCE(g2.Grocery2, 0) AS Grocery2,
    COALESCE(g1.Grocery1, 0) + COALESCE(g2.Grocery2, 0) AS TotalCost
FROM grocery1_total g1
FULL JOIN grocery2_total g2 ON g1.Name1 = g2.Name1
ORDER BY TotalCost DESC;

代码说明 #

• 使用CTE（公共表表达式）分别计算两个超市的个人费用总和，各自GROUP BY后不会产生重复记录
• 用COALESCE处理NULL值：如果某人只在一个超市消费，另一超市的费用显示为0
• 最终通过FULL JOIN确保包含仅在单个超市消费的人员，再按总费用降序排序

内容的提问来源于stack exchange，提问作者Greg-learner