Inner Join关联字段求和异常：如何获取正确整体合计值？

需求：对Inner Join关联的字段进行整体求和，但当前SQL查询未实现该效果，仅逐行返回字段值并累加，例如期望NumberOfPlants的总和为163,237、NumOfJornales的总和为61，实际却返回多行数据。

原SQL查询代码 #

select
   SUM(ACT.NumberOfPlants ) AS NumberOfPlants,
   SUM(ACT.NumOfJornales) AS NumberOfJornals
FROM dbo.AGRMastPlanPerformance MPR (NOLOCK)
INNER JOIN GENRegion GR ON (GR.intGENRegionKey = MPR.intGENRegionLink )
INNER JOIN AGRDetPlanPerformance DPR (NOLOCK) ON 
   (DPR.intAGRMastPlanPerformanceLink = MPR.intAGRMastPlanPerformanceKey)
INNER JOIN vwGENPredios P (NOLOCK) ON ( DPR.intGENPredioLink = P.intGENPredioKey )
INNER JOIN AGRSubActivity SA (NOLOCK) ON (SA.intAGRSubActivityKey = DPR.intAGRSubActivityLink)
LEFT JOIN (
    SELECT RA.intGENPredioLink, AR.intAGRActividadLink, 
           AR.intAGRSubActividadLink, SUM(AR.decNoPlantas) AS intPlantasTrabajads, 
           SUM(AR.decNoPersonas) AS NumOfJornales, SUM(AR.decNoPlants) AS NumberOfPlants
    FROM AGRRecordActivity RA WITH (NOLOCK)
    INNER JOIN AGRActividadRealizada AR WITH (NOLOCK) ON 
        (AR.intAGRRegistroActividadLink = RA.intAGRRegistroActividadKey AND AR.bitActivo = 1)
    INNER JOIN AGRSubActividad SA (NOLOCK) ON 
        (SA.intAGRSubActividadKey = AR.intAGRSubActividadLink AND SA.bitEnabled = 1)
    WHERE RA.bitActive = 1 AND AR.bitActive = 1 AND RA.intAGRTractorsCrewsLink IN(2)
    GROUP BY RA.intGENPredioLink,
             AR.decNoPersons,
             AR.decNoPlants,
             AR.intAGRAActivityLink,
             AR.intAGRSubActividadLink
) ACT ON (ACT.intGENPredioLink IN( DPR.intGENPredioLink) AND
          ACT.intAGRAActivityLink IN( DPR.intAGRAActivityLink) AND
          ACT.intAGRSubActivityLink IN( DPR.intAGRSubActivityLink))
WHERE
    MPR.intAGRMastPlanPerformanceKey IN(4) AND
    DPR.intAGRSubActivityLink IN( 1153)
GROUP BY
    P.vchRegion,
    ACT.NumberOfFloors,
    ACT.NumOfJournals
ORDER BY ACT.NumberOfFloors DESC

修改方案及说明 #

导致返回多行的核心问题是外层查询的GROUP BY子句，它会按指定字段分组统计，而非返回整体合计。同时子查询的分组逻辑也存在冗余，会导致重复计算。具体修改如下：

1. 移除外层的GROUP BY和ORDER BY：直接对关联后的ACT表字段求和，不需要分组，这样就能得到整体合计值。
2. 修正子查询的分组字段：子查询里不应该把AR.decNoPersons、AR.decNoPlants这类要聚合的字段加入分组，只保留关联所需的维度字段，避免生成过多明细行导致外层重复累加。
3. 优化关联条件的IN为=：对于单个值的匹配，用=更严谨，避免不必要的逻辑歧义。

修改后的SQL代码 #

select
   SUM(ACT.NumberOfPlants) AS NumberOfPlants,
   SUM(ACT.NumOfJornales) AS NumberOfJornals
FROM dbo.AGRMastPlanPerformance MPR (NOLOCK)
INNER JOIN GENRegion GR ON GR.intGENRegionKey = MPR.intGENRegionLink
INNER JOIN AGRDetPlanPerformance DPR (NOLOCK) ON 
   DPR.intAGRMastPlanPerformanceLink = MPR.intAGRMastPlanPerformanceKey
INNER JOIN vwGENPredios P (NOLOCK) ON DPR.intGENPredioLink = P.intGENPredioKey
INNER JOIN AGRSubActivity SA (NOLOCK) ON SA.intAGRSubActivityKey = DPR.intAGRSubActivityLink
LEFT JOIN (
    SELECT RA.intGENPredioLink, AR.intAGRActividadLink, 
           AR.intAGRSubActividadLink, SUM(AR.decNoPlantas) AS intPlantasTrabajads, 
           SUM(AR.decNoPersonas) AS NumOfJornales, SUM(AR.decNoPlants) AS NumberOfPlants
    FROM AGRRecordActivity RA WITH (NOLOCK)
    INNER JOIN AGRActividadRealizada AR WITH (NOLOCK) ON 
        AR.intAGRRegistroActividadLink = RA.intAGRRegistroActividadKey AND AR.bitActivo = 1
    INNER JOIN AGRSubActividad SA (NOLOCK) ON 
        SA.intAGRSubActividadKey = AR.intAGRSubActividadLink AND SA.bitEnabled = 1
    WHERE RA.bitActive = 1 AND AR.bitActive = 1 AND RA.intAGRTractorsCrewsLink = 2
    -- 只保留关联所需的分组字段，移除聚合字段
    GROUP BY RA.intGENPredioLink, AR.intAGRActividadLink, AR.intAGRSubActividadLink
) ACT ON 
    ACT.intGENPredioLink = DPR.intGENPredioLink AND
    ACT.intAGRActividadLink = DPR.intAGRAActivityLink AND
    ACT.intAGRSubActividadLink = DPR.intAGRSubActivityLink
WHERE
    MPR.intAGRMastPlanPerformanceKey = 4 AND
    DPR.intAGRSubActivityLink = 1153

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