SQL Group BY, Top N Items for each Group。

I have a SQL Query which gets gets top 5 sold items at a given store.

我有一个SQL查询，它在给定的商店中获得前五名的商品。

SELECT TOP 5 S.UPCCode, SUM(TotalDollarSales) FROM Sales S
WHERE S.StoreId = 1
GROUP BY S.UPCCode
ORDER BY SUM(S.TotalDollarSales) desc

The Sales table has -> UPCCode, SaleDate, StoreId, TotalDollarSales

销售表有-> UPCCode, SaleDate, StoreId, TotalDollarSales

I am looking for a query which will return me Top 5 items sold for each of the stores in a single query. I can write multiple queries and use a union but it doesn't seem efficient.

我正在寻找一个查询，它将返回在一个查询中为每个商店销售的前5项。我可以编写多个查询并使用一个联合，但它似乎没有效率。

How can I get the top 5 sold items for each store in a single query.

如何在一个查询中获得每个商店的前5个销售项目。

Thanks in advance.

提前谢谢。

2 个解决方案

#1

;WITH s AS 
(
  SELECT StoreID, UPCCode, tds, rn = ROW_NUMBER()
  OVER (PARTITION BY StoreID ORDER BY tds DESC)
  FROM 
  (
    SELECT StoreID, UPCCode, tds = SUM(TotalDollarSales)
    FROM Sales
    GROUP BY StoreID, UPCCode
  ) AS s2
)
SELECT StoreID, UPCCode, TotalDollarSales = tds
FROM s
WHERE rn <= 5
ORDER BY StoreID, TotalDollarSales DESC;

#2

try this:

试试这个:

select ss.StoreId,is.*
from (select distinct StoreId from Sales) ss
cross apply (SELECT TOP 5 S.UPCCode, SUM(TotalDollarSales) as SumTotalDollarSales FROM Sales S
             WHERE S.StoreId = ss.StoreId
             GROUP BY S.UPCCode
             ORDER BY SUM(S.TotalDollarSales) desc) is