SQL Group BY，每个组的前 N ​​个项目 [英] SQL Group BY, Top N Items for each Group

问题描述

我有一个 SQL 查询，它可以获取给定商店中最畅销的 5 件商品.

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

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

Sales 表有 -> UPCCode、SaleDate、StoreId、TotalDollarSales

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

我正在寻找一个查询，该查询将在单个查询中返回每个商店销售的前 5 件商品.我可以编写多个查询并使用联合，但似乎效率不高.

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.

提前致谢.

推荐答案

;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;

这篇关于SQL Group BY，每个组的前 N ​​个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！