AdventureWorks2008R2 SQL查询问题：总销售额计算结果异常求助

常见错误原因（导致TotalSalesAmount过大） #

• 多表连接引发重复累加：直接关联SalesOrderHeader和SalesOrderDetail后未先聚合订单，会导致每个订单的TotalDue被该订单内的产品行数重复计算。例如一个订单TotalDue为1000，包含5个产品，未处理的话SUM(TotalDue)会计算5次1000，最终总销售额被放大5倍。
• 分组逻辑错误：分组时未先按SalesOrderID聚合订单数据，而是直接按SalesPersonID分组，导致同一订单的TotalDue被多次统计。
• 关联Top3订单时的重复计算：如果在主查询中直接关联包含Top3订单的子查询，未做聚合处理，会导致员工的总销售额被重复计算（每个Top3订单对应一次累加）。

正确实现思路 #

1. 先计算员工核心聚合指标：单独统计每个员工的总销售额、最高订单值，同时关联订单明细统计售出的唯一产品数，避免重复累加订单金额。
2. 独立计算Top3订单：用窗口函数RANK()（支持并列排名）按员工分区，按订单总数量降序排序，筛选出每个员工的Top3订单。
3. 关联两个结果集：将员工聚合数据与Top3订单数据关联，最终输出需求结果。

示例正确代码 #

-- 计算员工核心销售指标：总销售额、最高订单值、唯一产品数
WITH EmployeeSalesMetrics AS (
    SELECT
        soh.SalesPersonID,
        SUM(soh.TotalDue) AS TotalSalesAmount,
        MAX(soh.TotalDue) AS HighestOrderValue,
        COUNT(DISTINCT sod.ProductID) AS UniqueProductsSold
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod 
        ON soh.SalesOrderID = sod.SalesOrderID
    WHERE soh.SalesPersonID IS NOT NULL -- 排除无销售员工的订单
    GROUP BY soh.SalesPersonID
    HAVING SUM(soh.TotalDue) > 9800000 -- 过滤总销售额超9800000的员工
),
-- 计算每个订单的总数量，并筛选每个员工的Top3订单（含并列）
EmployeeTopOrders AS (
    SELECT
        soh.SalesPersonID,
        soh.SalesOrderID,
        soh.TotalDue AS OrderTotal,
        SUM(sod.OrderQty) AS TotalOrderQuantity,
        RANK() OVER (
            PARTITION BY soh.SalesPersonID 
            ORDER BY SUM(sod.OrderQty) DESC
        ) AS OrderRank
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod 
        ON soh.SalesOrderID = sod.SalesOrderID
    WHERE soh.SalesPersonID IS NOT NULL
    GROUP BY soh.SalesPersonID, soh.SalesOrderID, soh.TotalDue
)
-- 关联结果并输出最终数据
SELECT
    esm.SalesPersonID,
    esm.UniqueProductsSold,
    esm.HighestOrderValue,
    esm.TotalSalesAmount,
    -- 将Top3订单信息合并为字符串（可根据需求调整格式）
    STRING_AGG(
        CONCAT(
            '订单ID: ', es.SalesOrderID,
            ' | 总数量: ', es.TotalOrderQuantity,
            ' | 订单金额: ', es.OrderTotal
        ), '; '
    ) AS Top3Orders
FROM EmployeeSalesMetrics esm
JOIN EmployeeTopOrders es 
    ON esm.SalesPersonID = es.SalesPersonID
WHERE es.OrderRank <= 3
GROUP BY esm.SalesPersonID, esm.UniqueProductsSold, esm.HighestOrderValue, esm.TotalSalesAmount
ORDER BY esm.TotalSalesAmount DESC;

关键细节说明 #

• 避免重复累加：EmployeeSalesMetrics中，虽然关联了SalesOrderDetail，但SUM(soh.TotalDue)是基于SalesOrderHeader的订单级数据分组计算，不会因为订单内的多个产品行重复统计金额。
• 并列排名处理：使用RANK()而非ROW_NUMBER()，确保订单总数量相同的情况下，多个订单都能进入Top3范围。
• 唯一产品数统计：COUNT(DISTINCT sod.ProductID)确保同一产品被同一员工多次售出时，仅统计一次。

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