按客户分组统计采购额、已付款额的SQL查询问题求助

问题背景 #

正在搭建支持客户自主选择付款周期（如每周）的物料采购数据库，需生成无重复客户的统计报表，展示每位客户的采购总额、欠款额及已付款额。

涉及三个数据表：

• Clients（客户信息表）
• Payments（付款记录表）
• OrdersInventory（采购物品及单价表）

表结构如下：

Clients    | Payments      | OrdersInventory | 
-----------| --------------|-----------------|
           | PaymentsID    | ItemID          |
ClientsID >|>ClientID      | Qty             |
           | PaymentAmount | NewPrice        |
           | OrderID      >|>OrderID         |

问题现象 #

同一客户有两笔付款记录（PaymentID 3为$10，PaymentID 4为$30），其中某订单包含两个物品，关联OrdersInventory表时，PaymentID 3的付款被重复显示：

ClientID | PaymentID | PaymentAmount | TotalItemsPrice
   1     |     3     |       $10     |    10
   1     |     3     |       $10     |    15
   1     |     4     |       $30     |    30

对TotalItemsPrice求和结果正确：

ClientID | PaymentID | PaymentAmount | TotalItemsPrice
   1     |     3     |       $10     |    25
   1     |     4     |       $30     |    30

但去除PaymentID后对PaymentAmount求和时，因关联导致重复计算，得到错误结果$50（期望为$40）：

实际结果：

ClientID | SumOfPaymentAmount | TotalItemsPrice
   1     |       $50          |    $55

期望结果：

ClientID | SumOfPaymentAmount | TotalItemsPrice
   1     |       $40          |    $55

当前使用的SQL代码：

SELECT Clients.ClientID, Sum(Payments.PaymentAmount) AS SumOfPaymentAmount, Sum([NewPrice]*[Qty]) AS TotalItemsPrice
FROM Clients RIGHT JOIN (Payments INNER JOIN OrdersInventory ON Payments.OrderID = OrdersInventory.OrderID) ON Clients.ClientID = Payments.ClientID
GROUP BY Clients.ClientID;

解决方案 #

问题根源是多对多关联导致的重复行：一个付款对应多个订单物品，关联后付款记录被复制多次，直接求和会重复计算付款金额。以下是两种可行解决方法：

方案1：子查询分别聚合（推荐） #

先分别对付款、订单物品按维度聚合，再关联结果，从根源避免重复行：

SELECT 
    c.ClientID,
    COALESCE(p.SumPayments, 0) AS SumOfPaymentAmount,
    COALESCE(o.TotalItemsPrice, 0) AS TotalItemsPrice,
    -- 直接计算欠款额
    COALESCE(o.TotalItemsPrice, 0) - COALESCE(p.SumPayments, 0) AS OutstandingAmount
FROM Clients c
LEFT JOIN (
    -- 按客户聚合付款总额
    SELECT ClientID, SUM(PaymentAmount) AS SumPayments
    FROM Payments
    GROUP BY ClientID
) p ON c.ClientID = p.ClientID
LEFT JOIN (
    -- 先按订单聚合单订单物品总价，再按客户聚合总采购额
    SELECT oi.ClientID, SUM(oi.OrderTotal) AS TotalItemsPrice
    FROM (
        SELECT 
            p.ClientID,
            oi.OrderID,
            SUM(oi.NewPrice * oi.Qty) AS OrderTotal
        FROM OrdersInventory oi
        JOIN Payments p ON oi.OrderID = p.OrderID
        GROUP BY p.ClientID, oi.OrderID
    ) oi
    GROUP BY oi.ClientID
) o ON c.ClientID = o.ClientID
WHERE c.ClientID IS NOT NULL; -- 仅保留有业务记录的客户

方案2：DISTINCT去重求和（简易场景） #

若仅需统计已有关联记录的客户，可通过付款唯一标识（PaymentID）去重，避免重复计算付款金额：

SELECT 
    Clients.ClientID,
    -- 利用PaymentID唯一标识付款记录，避免重复求和
    SUM(DISTINCT Payments.PaymentAmount * Payments.PaymentID) / SUM(DISTINCT Payments.PaymentID) AS SumOfPaymentAmount,
    SUM([NewPrice]*[Qty]) AS TotalItemsPrice,
    SUM([NewPrice]*[Qty]) - (SUM(DISTINCT Payments.PaymentAmount * Payments.PaymentID) / SUM(DISTINCT Payments.PaymentID)) AS OutstandingAmount
FROM Clients 
RIGHT JOIN (Payments INNER JOIN OrdersInventory ON Payments.OrderID = OrdersInventory.OrderID) 
    ON Clients.ClientID = Payments.ClientID
GROUP BY Clients.ClientID;

注：此方法仅在PaymentID为唯一主键时有效。

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