关联多销售表时如何避免重复，确保COUNT与SUM计算结果正确？

问题根源在于直接关联Sales.OrderLines和Sales.InvoiceLines会产生笛卡尔积：一个订单对应多条订单行，一个发票对应多条发票行，二者关联后会生成交叉行，导致SUM计算时重复统计同一金额，最终数值偏大。用SUM(DISTINCT)也不可取，因为如果存在不同行但金额相同的情况，会错误合并数据。

正确的做法是先在订单和发票级别分别聚合金额，再向上关联客户进行统计，具体SQL如下：

WITH OrderTotals AS (
    -- 先计算每个订单的总金额
    SELECT 
        OrderID, 
        SUM(Quantity * UnitPrice) AS OrderTotal
    FROM Sales.OrderLines
    GROUP BY OrderID
),
InvoiceTotals AS (
    -- 先计算每个发票的总金额，同时关联对应的OrderID
    SELECT 
        InvoiceID, 
        OrderID,
        SUM(Quantity * UnitPrice) AS InvoiceTotal
    FROM Sales.InvoiceLines
    GROUP BY InvoiceID, OrderID
)
SELECT
    c.CustomerID,
    c.CustomerName,
    COUNT(DISTINCT ot.OrderID) AS [number orders],
    COUNT(DISTINCT it.InvoiceID) AS [number invoices],
    SUM(ot.OrderTotal) AS [TOTAL orders],
    SUM(it.InvoiceTotal) AS [TOTAL invoices],
    SUM(ot.OrderTotal) - SUM(it.InvoiceTotal) AS [Amount Difference]
FROM Sales.Invoices i
-- 关联预聚合后的发票金额
JOIN InvoiceTotals it ON i.InvoiceID = it.InvoiceID
-- 关联预聚合后的订单金额
JOIN OrderTotals ot ON i.OrderID = ot.OrderID
-- 关联客户信息
JOIN Sales.Customers c ON i.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.CustomerName
ORDER BY c.CustomerID;

关键说明： #

1. 预聚合避免笛卡尔积：通过CTE分别在OrderLines和InvoiceLines层面按OrderID、InvoiceID聚合，得到每个订单/发票的唯一总金额，后续关联时不会产生重复行。
2. 符合需求的关联逻辑：使用INNER JOIN确保只统计已转为发票的订单（因为Sales.Invoices与Sales.Orders关联，只有存在发票的订单才会被纳入统计）。
3. 差值计算：直接用订单总金额减去发票总金额即可得到需求中的第7列。

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