如何将带WHERE条件的SUM计算结果嵌入多表关联SELECT查询

问题背景 #

需要对12列（MTDWAGES_1到MTDWAGES_12）执行带特定WHERE条件的SUM计算，将结果整合到SSRS 401K信息报表的主查询中。曾尝试创建视图获取SUM结果但未成功（已注释视图代码），后续还有其他列需按相同逻辑计算，需简洁高效的实现方案。

原测试代码：

/*
GO
CREATE VIEW [TOTAL CONTRIBUTIONS] AS
SELECT SUM(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3 + MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6 + MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9 + MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12) AS 'TOT CONTRI'
FROM [DB1].[dbo].[UPR30301]
WHERE PYRLRTYP LIKE '3' AND PAYROLCD LIKE '401K';
GO*/


SELECT A.EMPLOYID, LASTNAME, FRSTNAME, SOCSCNUM,BRTHDATE,STRTDATE,DEMPINAC AS 'Term Date', B.YEAR1,
FROM [DB1].[dbo].[UPR00100] A
INNER JOIN [DB1].[dbo].[UPR30301] B ON A.EMPLOYID = B.EMPLOYID
INNER JOIN [DB1].[dbo].[UPR30300] c ON A.EMPLOYID = C.EMPLOYID
--WHERE 
GROUP BY A.EMPLOYID, LASTNAME, FRSTNAME, SOCSCNUM, BRTHDATE, STRTDATE, DEMPINAC, B.YEAR1, B.PYRLRTYP, B.PAYROLCD

核心问题分析 #

之前的视图失败原因是未按员工分组，返回的是全局所有符合条件的记录总和，无法和主查询的单个员工关联。所有方案都需要确保聚合是按EMPLOYID（及可能的YEAR1）分组的。

解决方案 #

方案1：关联子查询（快速实现，单指标场景） #

直接在SELECT语句中嵌入子查询，计算每个员工的401K总贡献，无需额外对象：

SELECT 
    A.EMPLOYID, 
    LASTNAME, 
    FRSTNAME, 
    SOCSCNUM,
    BRTHDATE,
    STRTDATE,
    DEMPINAC AS 'Term Date', 
    B.YEAR1,
    -- 子查询计算当前员工的401K总贡献
    (SELECT SUM(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3 + MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6 + MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9 + MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12)
     FROM [DB1].[dbo].[UPR30301]
     WHERE EMPLOYID = A.EMPLOYID
       AND PYRLRTYP LIKE '3' 
       AND PAYROLCD LIKE '401K'
       AND YEAR1 = B.YEAR1) AS 'TOT CONTRI'
FROM [DB1].[dbo].[UPR00100] A
INNER JOIN [DB1].[dbo].[UPR30301] B ON A.EMPLOYID = B.EMPLOYID
INNER JOIN [DB1].[dbo].[UPR30300] C ON A.EMPLOYID = C.EMPLOYID
GROUP BY A.EMPLOYID, LASTNAME, FRSTNAME, SOCSCNUM, BRTHDATE, STRTDATE, DEMPINAC, B.YEAR1

方案2：CTE（多指标扩展友好） #

用公共表表达式（CTE）预计算所有需要的聚合指标，再和主查询关联，结构清晰，后续新增指标只需修改CTE：

WITH EmpContributions AS (
    SELECT 
        EMPLOYID,
        YEAR1,
        -- 401K总贡献
        SUM(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3 + MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6 + MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9 + MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12) AS 'TOT CONTRI',
        -- 后续新增其他指标，比如另一项福利的总和
        -- SUM(...) AS 'OTHER BENEFIT'
    FROM [DB1].[dbo].[UPR30301]
    WHERE PYRLRTYP LIKE '3' AND PAYROLCD LIKE '401K'
    GROUP BY EMPLOYID, YEAR1
)
SELECT 
    A.EMPLOYID, 
    LASTNAME, 
    FRSTNAME, 
    SOCSCNUM,
    BRTHDATE,
    STRTDATE,
    DEMPINAC AS 'Term Date', 
    B.YEAR1,
    EC.[TOT CONTRI]
    -- EC.[OTHER BENEFIT]
FROM [DB1].[dbo].[UPR00100] A
INNER JOIN [DB1].[dbo].[UPR30301] B ON A.EMPLOYID = B.EMPLOYID
INNER JOIN [DB1].[dbo].[UPR30300] C ON A.EMPLOYID = C.EMPLOYID
LEFT JOIN EmpContributions EC ON A.EMPLOYID = EC.EMPLOYID AND B.YEAR1 = EC.YEAR1
GROUP BY A.EMPLOYID, LASTNAME, FRSTNAME, SOCSCNUM, BRTHDATE, STRTDATE, DEMPINAC, B.YEAR1, EC.[TOT CONTRI]

方案3：修正后的视图（复用场景） #

如果后续多个查询需要用到该聚合数据，可创建按员工和年份分组的视图：

GO
CREATE VIEW [Emp401KContributions] AS
SELECT 
    EMPLOYID,
    YEAR1,
    SUM(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3 + MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6 + MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9 + MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12) AS 'TOT CONTRI'
FROM [DB1].[dbo].[UPR30301]
WHERE PYRLRTYP LIKE '3' AND PAYROLCD LIKE '401K'
GROUP BY EMPLOYID, YEAR1;
GO

主查询中直接关联视图：

SELECT 
    A.EMPLOYID, 
    LASTNAME, 
    FRSTNAME, 
    SOCSCNUM,
    BRTHDATE,
    STRTDATE,
    DEMPINAC AS 'Term Date', 
    B.YEAR1,
    VC.[TOT CONTRI]
FROM [DB1].[dbo].[UPR00100] A
INNER JOIN [DB1].[dbo].[UPR30301] B ON A.EMPLOYID = B.EMPLOYID
INNER JOIN [DB1].[dbo].[UPR30300] C ON A.EMPLOYID = C.EMPLOYID
LEFT JOIN [Emp401KContributions] VC ON A.EMPLOYID = VC.EMPLOYID AND B.YEAR1 = VC.YEAR1
GROUP BY A.EMPLOYID, LASTNAME, FRSTNAME, SOCSCNUM, BRTHDATE, STRTDATE, DEMPINAC, B.YEAR1, VC.[TOT CONTRI]

注意事项 #

• 若UPR30301中存在同一员工同一年份的多条记录，必须按EMPLOYID和YEAR1分组聚合，避免重复计算。
• 用LEFT JOIN替代INNER JOIN可确保即使员工无401K贡献，仍能出现在报表中（对应TOT CONTRI为NULL）。
• 后续新增其他列的聚合逻辑，只需在CTE/视图中添加对应的SUM计算即可，无需大幅修改主查询结构。

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