AdventureWorks数据库TSQL问题：按地区统计销售额及销售人员数排查

嘿，这个问题我太熟了！你现在的代码之所以会为每个销售人员生成单独行，核心原因是没有先对数据按销售地区和年份做预聚合——直接拿包含单个销售记录的原始数据去做PIVOT，PIVOT会把所有未被聚合的字段（比如销售人员ID）当成分组维度，自然就拆成了每行对应一个销售的结果。

要实现你想要的“按地区分组、展示各年度销售额+地区活跃销售人数”的效果，得分成两步走：先聚合数据，再做透视。下面给你两种可行的实现方案，都是基于AdventureWorks数据库的表结构来写的：

方案一：用CTE整合聚合与透视 #

WITH SalesTerritoryAggregates AS (
    -- 第一步：统计各地区的活跃销售人员数量
    SELECT 
        st.Name AS SalesTerritory,
        COUNT(DISTINCT sp.BusinessEntityID) AS SalesPeople,
        NULL AS OrderYear,
        NULL AS AnnualSales
    FROM Sales.SalesPerson sp
    JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    GROUP BY st.Name

    UNION ALL

    -- 第二步：统计各地区各年度的销售总额
    SELECT 
        st.Name AS SalesTerritory,
        NULL AS SalesPeople,
        YEAR(soh.OrderDate) AS OrderYear,
        SUM(soh.TotalDue) AS AnnualSales
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
    GROUP BY st.Name, YEAR(soh.OrderDate)
),
PivotReadyData AS (
    -- 把销售人数关联到每一行年度数据上，确保透视后每个地区只有一行
    SELECT 
        SalesTerritory,
        MAX(SalesPeople) OVER (PARTITION BY SalesTerritory) AS SalesPeople,
        OrderYear,
        AnnualSales
    FROM SalesTerritoryAggregates
    WHERE OrderYear IS NOT NULL
)
-- 执行透视，得到目标格式
SELECT 
    SalesTerritory,
    SalesPeople,
    [2011], [2012], [2013], [2014]
FROM PivotReadyData
PIVOT (
    SUM(AnnualSales)
    FOR OrderYear IN ([2011], [2012], [2013], [2014])
) AS PivotResult
ORDER BY SalesTerritory;

方案二：分开处理再关联（可读性更强） #

这个方案把“销售总额透视”和“销售人数统计”拆成两个独立的CTE，最后关联起来，逻辑更清晰：

-- 先生成各地区各年度销售额的透视表
WITH PivotedSalesData AS (
    SELECT 
        st.Name AS SalesTerritory,
        [2011], [2012], [2013], [2014]
    FROM (
        SELECT 
            st.Name,
            YEAR(soh.OrderDate) AS OrderYear,
            soh.TotalDue
        FROM Sales.SalesOrderHeader soh
        JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
    ) AS SourceData
    PIVOT (
        SUM(TotalDue)
        FOR OrderYear IN ([2011], [2012], [2013], [2014])
    ) AS PivotTable
),
-- 统计各地区的活跃销售人员数量
SalesPeopleByTerritory AS (
    SELECT 
        st.Name AS SalesTerritory,
        COUNT(DISTINCT sp.BusinessEntityID) AS SalesPeople
    FROM Sales.SalesPerson sp
    JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    GROUP BY st.Name
)
-- 关联两个结果集，得到最终格式
SELECT 
    psd.SalesTerritory,
    spt.SalesPeople,
    psd.[2011], psd.[2012], psd.[2013], psd.[2014]
FROM PivotedSalesData psd
JOIN SalesPeopleByTerritory spt ON psd.SalesTerritory = spt.SalesTerritory
ORDER BY psd.SalesTerritory;

关键要点总结 #

• 必须先按目标分组维度（销售地区、年份）做聚合，把原始的行级销售记录转换成每个分组的汇总数据，再进行PIVOT操作。
• 销售人数是地区维度的统计值，不是年度的，所以要单独聚合后再和透视后的销售额数据关联，或者用窗口函数把它映射到每一行年度数据上。

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