多表求和场景下OVER PARTITION BY子句使用问题求助

我来帮你搞定这个问题！你的SQL语句之所以得不到正确的求和结果，核心问题出在LEFT JOIN导致的数据行膨胀，让窗口函数计算出了错误的总和。

问题根源 #

当你直接把Table1和Table2做LEFT JOIN时，如果某条Table1的记录对应多条Table2的支付记录（比如一个订单有多笔付款），这条Table1的Cost值会在结果集中重复出现多次。这时候用SUM(Cost) OVER (PARTITION BY RepNumber)计算的是膨胀后所有行的Cost总和——相当于把同一个Cost值加了好几次，自然和你想要的「该Rep的实际总成本」不符。而付款总额虽然计算逻辑上是对的，但总成本已经失真了。

解决方法 #

这里给你两种靠谱的解决方案，你可以根据场景选择：

方案1：先分别汇总再关联（逻辑更直观）

先单独计算每个Rep的总成本，以及每个Rep的总付款额，再把两个结果集关联起来，从根源避免数据膨胀：

WITH RepTotalCosts AS (
    SELECT 
        RepNumber,
        ProductNumber,
        SUM(Cost) OVER (PARTITION BY RepNumber) AS TotalCost,
        Uidx
    FROM Table1
),
RepTotalPayments AS (
    SELECT 
        t1.RepNumber,
        ISNULL(SUM(t2.PaymentAmount), 0) AS TotalPayments
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t2.T1Uidx = t1.Uidx
    GROUP BY t1.RepNumber
)
SELECT 
    rtc.RepNumber,
    rtc.ProductNumber,
    rtc.TotalCost,
    rtp.TotalPayments
FROM RepTotalCosts rtc
INNER JOIN RepTotalPayments rtp ON rtc.RepNumber = rtp.RepNumber;

方案2：先汇总Table2的支付再关联（性能更优）

先把Table2中每个Table1记录对应的支付总额提前算出来，再和Table1关联，这样关联后的结果集不会有重复行，窗口函数就能正确计算总和：

WITH AggregatedPayments AS (
    SELECT 
        T1Uidx,
        SUM(PaymentAmount) AS PaymentPerT1
    FROM Table2
    GROUP BY T1Uidx
)
SELECT 
    t1.RepNumber,
    t1.ProductNumber,
    SUM(t1.Cost) OVER (PARTITION BY t1.RepNumber) AS TotalCost,
    ISNULL(SUM(ap.PaymentPerT1), 0) OVER (PARTITION BY t1.RepNumber) AS TotalPayments
FROM Table1 t1
LEFT JOIN AggregatedPayments ap ON ap.T1Uidx = t1.Uidx;

小提示 #

• 方案1逻辑清晰，适合新手理解和调试；
• 方案2减少了关联后的行数，在数据量较大的场景下性能会更好。

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