如何在多表关联SUM聚合查询中添加FloridaExpenses列及更新总计

假设你当前的查询结构类似下面这样（基于Junction1表关联California、Texas表做聚合计算）：

SELECT 
    j.ID,
    SUM(c.Expense) AS CaliforniaExpenses,
    SUM(t.Expense) AS TexasExpenses,
    SUM(ISNULL(c.Expense, 0) + ISNULL(t.Expense, 0)) AS TotalExpenses
FROM Junction1 j
LEFT JOIN California c ON j.ID = c.ID
LEFT JOIN Texas t ON j.ID = t.ID
GROUP BY j.ID;

要添加FloridaExpenses列并更新TotalExpenses的计算逻辑，只需按以下方式修改：

SELECT 
    j.ID,
    SUM(c.Expense) AS CaliforniaExpenses,
    SUM(t.Expense) AS TexasExpenses,
    -- 新增Florida费用统计列
    SUM(ISNULL(f.Expense, 0)) AS FloridaExpenses,
    -- 更新总和，加入Florida的费用值
    SUM(ISNULL(c.Expense, 0) + ISNULL(t.Expense, 0) + ISNULL(f.Expense, 0)) AS TotalExpenses
FROM Junction1 j
LEFT JOIN California c ON j.ID = c.ID
LEFT JOIN Texas t ON j.ID = t.ID
-- 关联Florida表，用LEFT JOIN保证无对应记录的ID仍能保留
LEFT JOIN Florida f ON j.ID = f.ID
GROUP BY j.ID;

重要细节说明 #

• 用LEFT JOIN关联Florida表：如果某个ID在Florida表中没有匹配的记录，该行不会被过滤掉，此时FloridaExpenses会显示为0（因为用了ISNULL(f.Expense, 0)）
• ISNULL的作用：避免NULL值干扰聚合计算——如果不处理NULL，当某张表无匹配记录时，SUM结果会是NULL，且总和计算也会变成NULL

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