请求编写SQL查询获取AdventureWorks2008R2中每年销量TOP3产品

本人是SQL新手，需要编写SQL查询从MySQL的AdventureWorks2008R2数据库中，依据SalesOrderDetail表的OrderQty计算产品年度总销量，筛选出每年总销量排名前三的产品，同时计算对应年度的总销售额，并按指定格式返回数据（示例格式如下）：
Year Total Sale Top5Products
2005 1598 709, 712, 715
2006 5703 863, 715, 712
2007 9750 712, 870, 711
2008 8028 870, 712, 711
当前尝试的SQL代码无法实现需求，寻求帮助。

尝试的代码 #

USE "AdventureWorks2008R2";

WITH temp as
(
        SELECT YEAR(so.OrderDate) AS Year,so.TotalDue,ProductID,OrderQty,
        DENSE_RANK() OVER(PARTITION BY sod.ProductID ORDER BY OrderQty DESC) AS [Max_Order_Rank]
        FROM Sales.SalesOrderDetail sod
        JOIN Sales.SalesOrderHeader so ON 
            sod.SalesOrderID = so.SalesOrderID
        GROUP BY YEAR(so.OrderDate),ProductID,TotalDue,OrderQty
)
SELECT * FROM temp
ORDER BY Max_Order_Rank DESC;

问题分析 #

你的代码存在几个核心问题：

• 分组逻辑错误：按YEAR(so.OrderDate),ProductID,TotalDue,OrderQty分组并没有实现年度销量聚合，反而保留了每条订单明细，无法得到产品年度总销量。
• 窗口分区错误：PARTITION BY sod.ProductID是按产品而非年份分区，导致排名是产品跨年份的销量排序，不是年度内的销量排名。
• 缺少关键计算：没有聚合年度总销售额，也没有将前三产品ID拼接成指定的逗号分隔格式。

正确SQL代码 #

USE AdventureWorks2008R2;

-- 1. 计算每个产品的年度总销量，以及对应年度的总销售额
WITH ProductYearlySales AS (
    SELECT
        YEAR(so.OrderDate) AS SaleYear,
        sod.ProductID,
        SUM(sod.OrderQty) AS TotalQty,
        SUM(so.TotalDue) OVER (PARTITION BY YEAR(so.OrderDate)) AS AnnualTotalSale
    FROM Sales.SalesOrderDetail sod
    JOIN Sales.SalesOrderHeader so 
        ON sod.SalesOrderID = so.SalesOrderID
    GROUP BY YEAR(so.OrderDate), sod.ProductID
),
-- 2. 对每个年度的产品销量进行排名
RankedProducts AS (
    SELECT
        SaleYear,
        ProductID,
        TotalQty,
        AnnualTotalSale,
        DENSE_RANK() OVER (PARTITION BY SaleYear ORDER BY TotalQty DESC) AS SalesRank
    FROM ProductYearlySales
)
-- 3. 筛选年度前三产品，拼接成要求的格式
SELECT
    SaleYear AS `Year`,
    MAX(AnnualTotalSale) AS `Total Sale`,
    GROUP_CONCAT(ProductID ORDER BY SalesRank SEPARATOR ', ') AS Top3Products
FROM RankedProducts
WHERE SalesRank <= 3
GROUP BY SaleYear
ORDER BY SaleYear;

代码说明 #

• ProductYearlySales：通过GROUP BY按年份和产品ID聚合，得到每个产品的年度总销量；用窗口函数SUM(so.TotalDue) OVER (PARTITION BY YEAR(so.OrderDate))计算对应年度的总销售额。
• RankedProducts：按年份分区，对每个年度内的产品按总销量降序排名，DENSE_RANK()保证并列排名不占用后续名次。
• 最终查询：筛选排名前三的产品，用GROUP_CONCAT将产品ID按排名顺序拼接成字符串，取年度总销售额（同一年度该值一致，用MAX即可），最后按年份排序输出。

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