如何在AdventureWorks数据库中计算各区域人员年度销售平均值？

嘿，我来帮你搞定这个查询需求！你需要按销售区域、销售人员展示2011-2014年的销售额及平均销售额，还要处理单行多列的聚合场景，下面给你详细拆解：

需求分析 #

核心是把单行内的年度销售列数据做聚合，按销售区域和销售人员分组，同时输出各年度明细和平均销售额。我们可以从三种实现方式里选最适合的，先给你对比下场景：

• CTE（公用表表达式）：最推荐！代码结构清晰，逻辑分层明显，不需要额外临时存储，适合一次性查询，调试维护都方便。
• 临时表：如果这个中间结果需要被多次查询复用，或者数据量极大需要加索引优化性能，就用临时表，它能持久化中间结果提升多次查询效率。
• 子查询：尽量别用多层嵌套，会让代码臃肿难读，排查问题费劲，仅适合非常简单的场景。

具体T-SQL实现 #

场景1：已有单行多列的年度销售统计数据 #

假设你已经有一张汇总好的表（比如Sales.SalesPersonStats），每行对应一个销售人员，包含Sales2011、Sales2012等年度销售额列，用CTE实现的代码如下：

WITH SalesCTE AS (
    SELECT
        st.Name AS SalesTerritory,
        CONCAT(p.FirstName, ' ', p.LastName) AS SalesPeople,
        -- 用ISNULL处理NULL值，避免平均计算出错
        ISNULL(sps.Sales2011, 0) AS [2011],
        ISNULL(sps.Sales2012, 0) AS [2012],
        ISNULL(sps.Sales2013, 0) AS [2013],
        ISNULL(sps.Sales2014, 0) AS [2014]
    FROM
        Sales.SalesPersonStats sps
    JOIN Sales.SalesPerson sp ON sps.BusinessEntityID = sp.BusinessEntityID
    JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    JOIN Person.Person p ON sp.BusinessEntityID = p.BusinessEntityID
)
SELECT
    SalesTerritory,
    SalesPeople,
    [2011],
    [2012],
    [2013],
    [2014],
    -- 用4.0确保浮点运算，ROUND保留两位小数
    ROUND(([2011] + [2012] + [2013] + [2014]) / 4.0, 2) AS AvgSales
FROM
    SalesCTE
ORDER BY
    SalesTerritory, SalesPeople;

场景2：从原始订单数据聚合年度销售额 #

如果需要从AdventureWorks的原始订单表（Sales.SalesOrderHeader）中统计年度数据，再转成列展示，用CTE+PIVOT的方式实现：

WITH AnnualSalesCTE AS (
    SELECT
        st.Name AS SalesTerritory,
        CONCAT(p.FirstName, ' ', p.LastName) AS SalesPeople,
        YEAR(soh.OrderDate) AS SaleYear,
        SUM(soh.TotalDue) AS AnnualSales
    FROM
        Sales.SalesOrderHeader soh
    JOIN Sales.SalesPerson sp ON soh.SalesPersonID = sp.BusinessEntityID
    JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    JOIN Person.Person p ON sp.BusinessEntityID = p.BusinessEntityID
    WHERE
        YEAR(soh.OrderDate) BETWEEN 2011 AND 2014
    GROUP BY
        st.Name, CONCAT(p.FirstName, ' ', p.LastName), YEAR(soh.OrderDate)
)
SELECT
    SalesTerritory,
    SalesPeople,
    ISNULL([2011], 0) AS [2011],
    ISNULL([2012], 0) AS [2012],
    ISNULL([2013], 0) AS [2013],
    ISNULL([2014], 0) AS [2014],
    ROUND((ISNULL([2011], 0) + ISNULL([2012], 0) + ISNULL([2013], 0) + ISNULL([2014], 0)) / 4.0, 2) AS AvgSales
FROM
    AnnualSalesCTE
PIVOT (
    SUM(AnnualSales)
    FOR SaleYear IN ([2011], [2012], [2013], [2014])
) AS PivotTable
ORDER BY
    SalesTerritory, SalesPeople;

结果说明 #

执行后会得到你想要的格式：

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