使用内连接时SQL SUM函数出现重复求和问题求助

你遇到的重复计算是因为直接连接tblhourly_stats和tblQuickPlans时产生了笛卡尔积。比如tblhourly_stats对应某媒体有N条记录，tblQuickPlans对应同一媒体有M条记录，连接后会生成N*M条记录，求和时每个表的数值会被重复计算多次，最终结果被放大。

正确的做法是先分别对两个表按媒体和日期范围分组求和，得到各自的聚合结果，再将这两个聚合结果与tblMedias连接，避免重复计算：

SELECT 
    m.Media_Name,
    m.Prod_Rate,
    COALESCE(qp.TotalHours, 0) AS TotalHours,
    COALESCE(hs.ActTotal, 0) AS ActTotal,
    COALESCE(hs.ClrdTotal, 0) AS ClrdTotal,
    COALESCE(hs.InProcTotal, 0) AS InProcTotal,
    COALESCE(hs.EscTotal, 0) AS EscTotal
FROM [tblMedias] m
LEFT JOIN (
    -- 预计算tblQuickPlans的总时长
    SELECT 
        media,
        SUM(CAST(TotalHours AS decimal(10,2))) AS TotalHours
    FROM [tblQuickPlans]
    WHERE [Date] BETWEEN @startDate AND @endDate
    GROUP BY media
) qp ON m.Media_Name = qp.media
LEFT JOIN (
    -- 预计算tblhourly_stats的各项统计值
    SELECT 
        Media,
        SUM(CAST(ActTotal AS decimal(10,2))) AS ActTotal,
        SUM(CAST(ClrdTotal AS decimal(10,2))) AS ClrdTotal,
        SUM(CAST(InProcTotal AS decimal(10,2))) AS InProcTotal,
        SUM(CAST(EscTotal AS decimal(10,2))) AS EscTotal
    FROM [tblhourly_stats]
    WHERE Media = @passedmedia
      AND [Date] BETWEEN CAST(@startDate AS datetime) AND CAST(@endDate AS datetime)
    GROUP BY Media
) hs ON m.Media_Name = hs.Media
WHERE m.Media_Name = @passedmedia;

说明 #

• 子查询qp和hs各自完成单表的聚合计算，从根源上避免了多表连接产生的笛卡尔积
• 使用LEFT JOIN保证即使其中一个表无匹配数据，仍能返回另一表的结果（若需严格过滤掉无匹配的情况，可改为INNER JOIN）
• COALESCE函数用于处理NULL值，确保无数据时显示0而非NULL

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