访问组中的前n个

Question

我有一个表格，我需要在其中获取每个类别的前n个最高金额的项目。

Category Item  InventoryCount
-------  ----- ------------- 
Beverage  milk    3  
Beverage  water   2 
Beverage  beer    9 
Utensil   fork    7 
Utensil   spoon   2 
Utensil   knife   1 
Utensil   spork   4 

我期望的输出是前2个类别中的最高清单。

Category Item  InventoryCount
-------  ----- ------------- 
Beverage  beer   9 
Beverage  milk   3 
Utensil   fork   7 
Utensil  spork   4 

Answer 1

这应该为您工作。如果它不满足您的要求，请发回您需要的内容。您最初希望有25个，所以您只需将最后一个子句修改为HAVING COUNT(*) <= 25

SELECT  a.item, 
        a.category, 
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, 
          a.item, 
          a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.category, COUNT(*) DESC

如果要从表中选择更多列，只需将它们添加到SELECT和“ GROUP BY”子句中。

仅当您要扩展“ 每个Category，foo，bar的TOP n ”时，才将这些列也添加到该INNER JOIN子句中。

--show the top 2 items for each category and year.
SELECT  a.item, 
        a.category, 
        a.year,
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.year = b.year) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, a.item, a.year, a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.year, a.category, COUNT(*) DESC