SQL SUM计算返回重复行而非单一求和结果的技术求助

我来帮你分析下问题根源，再给出适配不同需求的解决方案：

你之所以得到大量重复记录，是因为用了sum() over ()这种无分区的窗口函数——它会为查询结果里的每一行都计算整个结果集的总和。而你的多表JOIN操作会返回多条匹配行，所以每一行都会重复显示相同的总和值，自然就出现了重复记录。

方案1：仅获取全局总求和值（不需要分支名称） #

如果你的目标只是得到两个列的总求和，不需要b.[Name]，直接用普通聚合函数即可，完全不需要窗口函数：

SELECT 
    SUM(td.detailNumber) AS Totaldetail, 
    SUM(t.Original) AS TotalOriginal 
FROM Detail td WITH(NOLOCK)
INNER JOIN Ticket t WITH(NOLOCK) ON t.ID = td.ID
INNER JOIN [system].Branch b WITH(NOLOCK) ON t.BranchID = b.BranchID
INNER JOIN [Option] mo WITH(NOLOCK) ON mo.OptionID = td.MID  -- 补全表别名，避免列名歧义
WHERE (td.ResultDateTime >= '2018/03/10 00:00:00:000' AND td.ResultDateTime <= '你的结束时间')

方案2：按分支分组获取每个分支的求和值 #

如果需要同时看到每个分支的名称和该分支对应的求和值，用GROUP BY分组聚合就可以了：

SELECT 
    b.[Name],
    SUM(td.detailNumber) AS Totaldetail, 
    SUM(t.Original) AS TotalOriginal 
FROM Detail td WITH(NOLOCK)
INNER JOIN Ticket t WITH(NOLOCK) ON t.ID = td.ID
INNER JOIN [system].Branch b WITH(NOLOCK) ON t.BranchID = b.BranchID
INNER JOIN [Option] mo WITH(NOLOCK) ON mo.OptionID = td.MID
WHERE (td.ResultDateTime >= '2018/03/10 00:00:00:000' AND td.ResultDateTime <= '你的结束时间')
GROUP BY b.[Name]

方案3：同时显示分支名称和全局总求和值 #

如果需求是既看到每个分支的名称，又看到整个结果集的总求和（不是每个分支的），可以用两种方式实现：

方法A：用DISTINCT快速去重 #

通过DISTINCT过滤掉重复的总和记录：

SELECT DISTINCT
    b.[Name],
    SUM(td.detailNumber) OVER () AS Totaldetail, 
    SUM(t.Original) OVER () AS TotalOriginal 
FROM Detail td WITH(NOLOCK)
INNER JOIN Ticket t WITH(NOLOCK) ON t.ID = td.ID
INNER JOIN [system].Branch b WITH(NOLOCK) ON t.BranchID = b.BranchID
INNER JOIN [Option] mo WITH(NOLOCK) ON mo.OptionID = td.MID
WHERE (td.ResultDateTime >= '2018/03/10 00:00:00:000' AND td.ResultDateTime <= '你的结束时间')

方法B：子查询拆分逻辑（更清晰） #

先单独计算全局总求和，再关联去重后的分支名称，逻辑更直观：

SELECT 
    branch_names.[Name],
    global_totals.Totaldetail,
    global_totals.TotalOriginal
FROM (
    -- 单独计算全局总求和
    SELECT 
        SUM(td.detailNumber) AS Totaldetail, 
        SUM(t.Original) AS TotalOriginal 
    FROM Detail td WITH(NOLOCK)
    INNER JOIN Ticket t WITH(NOLOCK) ON t.ID = td.ID
    INNER JOIN [Option] mo WITH(NOLOCK) ON mo.OptionID = td.MID
    WHERE (td.ResultDateTime >= '2018/03/10 00:00:00:000' AND td.ResultDateTime <= '你的结束时间')
) AS global_totals
CROSS JOIN (
    -- 获取符合条件的唯一分支名称
    SELECT DISTINCT b.[Name]
    FROM Detail td WITH(NOLOCK)
    INNER JOIN Ticket t WITH(NOLOCK) ON t.ID = td.ID
    INNER JOIN [system].Branch b WITH(NOLOCK) ON t.BranchID = b.BranchID
    INNER JOIN [Option] mo WITH(NOLOCK) ON mo.OptionID = td.MID
    WHERE (td.ResultDateTime >= '2018/03/10 00:00:00:000' AND td.ResultDateTime <= '你的结束时间')
) AS branch_names

最后提个小细节：你原来的SQL里INNER JOIN Option mo WITH(NOLOCK) ON OptionID = td.MID这里，OptionID前面最好加上表别名mo.，否则如果其他关联表也有同名的OptionID列，会触发列名歧义错误。

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