验证折扣后产品销售金额计算SQL查询的正确性

我有两张业务表：

• SalesLine：每行对应发票的一条明细，包含商品编码、销售数量、行金额等字段
• SalesHeader：每行对应一张完整发票，包含单据折扣信息，通过DocNum字段与SalesLine关联

需求是计算每个商品的累计销售总量和折扣后的实际销售总金额，现验证以下SQL查询的正确性，排查是否存在遗漏：

原查询语句 #

SELECT 
    SL.ItemCode, 
    SUM(SL.Qty) AS TotalQuantity,  

    SUM(SL.LineSum) - SUM(ISNULL(SH.DocDiscount, 0) * (SL.LineSum / ISNULL(TotalInvoiceSum.TotalSum, 0))) AS TotalSalesAfterDiscount ,
    COUNT(DISTINCT SL.DocNum) AS InvoiceCount  
FROM SalesLine SL
JOIN SalesHeader SH ON SL.DocNum = SH.DocNum  
JOIN (
    -- 计算每张发票的总金额
    SELECT DocNum, SUM(LineSum) AS TotalSum
    FROM SalesLine
    GROUP BY DocNum
) AS TotalInvoiceSum ON SL.DocNum = TotalInvoiceSum.DocNum
GROUP BY SL.ItemCode;

示例数据 #

SalesLine表 #

SalesHeader表 #

原查询的潜在问题 #

1. 除零风险：当某张发票的TotalSum为0时，SL.LineSum / TotalInvoiceSum.TotalSum会触发除零错误，ISNULL无法处理这种场景。
2. 数据丢失风险：使用JOIN关联SalesHeader会丢失没有对应发票头的销售明细，若存在发票头未录入的情况，这部分数据会被排除。
3. 精度误差：逐行计算折扣分摊后再求和，可能因浮点运算累积精度误差。
4. 折扣逻辑歧义：示例中DocDiscount存在正负值（如-33），原查询默认按百分比比例分摊，但未明确是百分比折扣还是金额折扣，逻辑可能不符合业务实际。

优化后的查询方案 #

场景1：DocDiscount为百分比折扣（如37表示37%折扣，-33表示加价33%） #

SELECT 
    SL.ItemCode,
    SUM(SL.Qty) AS TotalQuantity,
    -- 直接按行金额乘以折扣比例，避免逐行分摊的精度问题
    SUM(SL.LineSum * (1 - ISNULL(SH.DocDiscount, 0) / 100)) AS TotalSalesAfterDiscount,
    COUNT(DISTINCT SL.DocNum) AS InvoiceCount
FROM SalesLine SL
LEFT JOIN SalesHeader SH ON SL.DocNum = SH.DocNum
GROUP BY SL.ItemCode;

场景2：DocDiscount为总金额折扣（直接减免的固定金额） #

WITH InvoiceTotals AS (
    SELECT 
        DocNum,
        SUM(LineSum) AS TotalInvoiceSum,
        ISNULL(SH.DocDiscount, 0) AS DocDiscount
    FROM SalesLine SL
    LEFT JOIN SalesHeader SH ON SL.DocNum = SH.DocNum
    GROUP BY SL.DocNum, ISNULL(SH.DocDiscount, 0)
)
SELECT 
    SL.ItemCode,
    SUM(SL.Qty) AS TotalQuantity,
    -- 先计算发票级别的折扣总额，再按行金额比例分摊
    SUM(SL.LineSum - (SL.LineSum / NULLIF(IT.TotalInvoiceSum, 0)) * IT.DocDiscount) AS TotalSalesAfterDiscount,
    COUNT(DISTINCT SL.DocNum) AS InvoiceCount
FROM SalesLine SL
JOIN InvoiceTotals IT ON SL.DocNum = IT.DocNum
GROUP BY SL.ItemCode;

关键优化点 #

• 用LEFT JOIN替代JOIN，确保所有销售明细都被统计，避免丢失无对应发票头的数据。
• 用NULLIF(IT.TotalInvoiceSum, 0)处理除零场景，当发票总金额为0时，折扣分摊自动为0。
• 金额折扣场景下先聚合发票级别的总数据，再分摊到商品，提升计算精度和查询效率。

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