编写查询来检索每年排名前三的产品.使用SalesOrderDetail的OrderQty来计算总销售数量

Question

排名前三的产品总销量最高。同时计算前三名产品的总销量。以以下格式返回数据。销售总额top52005 1598 709,712,7152006 5703 863, 715,7122007 9750 712,870,7112008 8028 870,712,711

使用"AdventureWorks2008R2"MySQL

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;

我对sql很陌生，我不知道如何获得特定年份的前3名产品。

Answer 1

您可以使用row_number()获取每年排名前三的产品:

select year, productid, total_sold
from (select year, productid, sum(orderqty) as total_sold,
row_number() over (partition by year order by sum(orderqty) desc) as seqnum
from temp
group by year, productid
) yp
where seqnum <= 3;