SQL Server多表关联时无需GROUP BY获取列SUM()值的方法？

在SQL Server中，确实可以不用GROUP BY实现分组求和，核心方案是使用窗口函数（Window Functions），同时需要注意：你当前统计SUM值异常的主要原因是多表关联带来的重复行，而非列唯一导致。

一、保留明细行同时显示分组汇总值 #

使用SUM() OVER (PARTITION BY 分组字段)语法，既能保留所有关联后的明细行，又能计算对应分组的总和：

SELECT
    c.CrowdfundID,
    cu.ID AS CrowdfundedUserID,
    cu.Amount AS UserPledgeAmount,
    cpt.Invoice,
    cpt.Amount AS TransactionAmount,
    -- 按CrowdfundID分组的用户承诺总金额
    SUM(cu.Amount) OVER (PARTITION BY c.CrowdfundID) AS TotalUserPledge,
    -- 按CrowdfundID分组的交易总金额
    SUM(cpt.Amount) OVER (PARTITION BY c.CrowdfundID) AS TotalTransaction
FROM 
    Crowdfund c
INNER JOIN 
    CrowdfundedUser cu ON c.CrowdfundID = cu.CrowdfundID
INNER JOIN 
    CrowdfundiPaymentTransaction cpt ON cu.ID = cpt.CrowdfundedUserID

二、仅获取分组汇总结果（无需GROUP BY） #

如果只需要每个CrowdfundID的汇总值，有两种可靠方式：

方式1：DISTINCT + 窗口函数

SELECT DISTINCT
    c.CrowdfundID,
    SUM(cu.Amount) OVER (PARTITION BY c.CrowdfundID) AS TotalUserPledge,
    SUM(cpt.Amount) OVER (PARTITION BY c.CrowdfundID) AS TotalTransaction
FROM 
    Crowdfund c
INNER JOIN 
    CrowdfundedUser cu ON c.CrowdfundID = cu.CrowdfundID
INNER JOIN 
    CrowdfundiPaymentTransaction cpt ON cu.ID = cpt.CrowdfundedUserID

方式2：CTE预聚合（解决重复计算问题）

由于CrowdfundedUser和CrowdfundiPaymentTransaction是一对多关联，直接JOIN会导致cu.Amount被重复统计，更准确的做法是先分别聚合子表：

WITH UserPledgeSummary AS (
    SELECT 
        CrowdfundID,
        SUM(Amount) AS TotalUserPledge
    FROM CrowdfundedUser
    GROUP BY CrowdfundID
),
TransactionSummary AS (
    SELECT 
        cu.CrowdfundID,
        SUM(cpt.Amount) AS TotalTransaction
    FROM CrowdfundedUser cu
    INNER JOIN CrowdfundiPaymentTransaction cpt ON cu.ID = cpt.CrowdfundedUserID
    GROUP BY cu.CrowdfundID
)
SELECT
    c.CrowdfundID,
    ups.TotalUserPledge,
    ts.TotalTransaction
FROM Crowdfund c
LEFT JOIN UserPledgeSummary ups ON c.CrowdfundID = ups.CrowdfundID
LEFT JOIN TransactionSummary ts ON c.CrowdfundID = ts.CrowdfundID

额外提示 #

• 原查询中SUM(cpt..Amount)存在语法错误，需修正为SUM(cpt.Amount)；
• 多表一对多关联时，直接JOIN会产生重复行，导致SUM结果偏大，优先用预聚合的方式处理。

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