SQL Server中SUM函数与分组查询时记录合并异常问题

核心原因很明确：SQL的GROUP BY会严格按照分组列的实际值进行分组，包括NULL值——因为SQL里NULL不等于任何值，哪怕是另一个NULL。所以如果两条本该合并的记录中，PC.PAYTRANSCODEIDST或PC.PAYTRANSCODEIDOT存在值差异（比如一个是NULL一个非NULL，或者两者字面值不同），就会被拆成不同分组，导致SUM(PAYTIME)无法合并。

下面给出几种适配业务需求的解决思路：

1. 统一NULL值，强制合并分组 #

如果NULL属于无效值，可将其替换为统一占位符（比如空字符串或特定编码），让分组逻辑把这些值视为相同项：

SELECT 
    ISNULL(E.EMPID, 0) AS EMPID, 
    SUM(T.PAYTIME) AS PAYTIME, 
    ISNULL(PC.PAYTRANSCODEIDST, '') AS PAYTRANSCODEIDST, 
    ISNULL(PC.PAYTRANSCODEIDOT, '') AS PAYTRANSCODEIDOT, 
    T.DispatchDate, 
    ISNULL(CostCenterID, '10109') AS CostCenterID
FROM
    TimeRecord T
INNER JOIN Employee E ON E.EmpId = T.EmpId
INNER JOIN Paycode PC ON PC.Paycode = T.Paycode
INNER JOIN EmpType ET ON ET.EMPID = T.EMPID
WHERE 
    ET.STARTDATE <= '2024-09-29' 
    AND (ET.ENDDATE IS NULL OR ET.ENDDATE >= '2024-09-29')
    AND E.EMPID = 3129 
    AND T.DISPATCHDATE = '2024-09-27'
GROUP BY  
    E.EMPID, 
    ISNULL(CostCenterID, '10109'), 
    ISNULL(PC.PAYTRANSCODEIDST, ''), 
    ISNULL(PC.PAYTRANSCODEIDOT, ''), 
    T.DISPATCHDATE
ORDER BY  
    E.EmpId, 
    ISNULL(CostCenterID, '10109'), 
    ISNULL(PC.PAYTRANSCODEIDST, ''), 
    ISNULL(PC.PAYTRANSCODEIDOT, ''), 
    T.DispatchDate

⚠️ 注意：GROUP BY里的列处理逻辑必须和SELECT中完全一致，否则会触发语法错误。

2. 提取分组内的代表性值（无需加入GROUP BY） #

如果同一分组内PAYTRANSCODEIDST/PAYTRANSCODEIDOT有多个值，但业务只需保留其中一个（比如非NULL值、最大值/最小值），可以用聚合函数直接提取，不用把这两列放进GROUP BY：

SELECT 
    ISNULL(E.EMPID, 0) AS EMPID, 
    SUM(T.PAYTIME) AS PAYTIME, 
    MAX(PC.PAYTRANSCODEIDST) AS PAYTRANSCODEIDST, 
    MAX(PC.PAYTRANSCODEIDOT) AS PAYTRANSCODEIDOT, 
    T.DispatchDate, 
    ISNULL(CostCenterID, '10109') AS CostCenterID
FROM
    TimeRecord T
INNER JOIN Employee E ON E.EmpId = T.EmpId
INNER JOIN Paycode PC ON PC.Paycode = T.Paycode
INNER JOIN EmpType ET ON ET.EMPID = T.EMPID
WHERE 
    ET.STARTDATE <= '2024-09-29' 
    AND (ET.ENDDATE IS NULL OR ET.ENDDATE >= '2024-09-29')
    AND E.EMPID = 3129 
    AND T.DISPATCHDATE = '2024-09-27'
GROUP BY  
    E.EMPID, 
    ISNULL(CostCenterID, '10109'), 
    T.DispatchDate
ORDER BY  
    E.EmpId, 
    ISNULL(CostCenterID, '10109'), 
    T.DispatchDate

这种方式既保留了业务需要的列，又不会因为值差异拆分分组。

3. 排查数据的隐性差异 #

如果上面的方法都无效，先确认两条待合并记录的目标列是否真的应该被视为相同值：
运行无分组的原始数据查询，查看具体值：

SELECT 
    E.EMPID, T.PAYTIME, 
    PC.PAYTRANSCODEIDST, PC.PAYTRANSCODEIDOT, 
    T.DispatchDate, ISNULL(CostCenterID, '10109') AS CostCenterID
FROM
    TimeRecord T
INNER JOIN Employee E ON E.EmpId = T.EmpId
INNER JOIN Paycode PC ON PC.Paycode = T.Paycode
INNER JOIN EmpType ET ON ET.EMPID = T.EMPID
WHERE 
    ET.STARTDATE <= '2024-09-29' 
    AND (ET.ENDDATE IS NULL OR ET.ENDDATE >= '2024-09-29')
    AND E.EMPID = 3129 
    AND T.DISPATCHDATE = '2024-09-27'

检查是否存在肉眼难以发现的差异：比如字符串前后的空格、大小写不一致、隐藏特殊字符等。如果有，先清洗数据（比如用LTRIM(RTRIM())处理字符串）再分组。

额外优化提示 #

把老旧的逗号分隔表写法换成显式JOIN，代码可读性和维护性会提升很多（上面的示例已经做了替换）。

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