访问组中的前n个 [英] Access top n in group

问题描述

我有一个表格，需要在其中获取每个类别的前n个数量最多的商品.

I have a table where I need to get the top n highest amount items for each Category.

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个类别中的最高库存.

My desired output is the highest Inventory of the topmost 2 Categories.

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

推荐答案

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

This should work for you. If it doesn't satisfy your requirements, post back what you need. Your original desire was to have 25, so you'd simply modify the last clause to be 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'子句中即可.

If you wanted to select more columns from the table, simply add them to the SELECT and `GROUP BY' clauses.

仅当您要扩展每个 bar， 类别的 TOP n时，才将这些列也添加到INNER JOIN子句中.

Only when you want to expand the "TOP n for each Category, foo, bar", then you would add those columns to the INNER JOIN clause as well.

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

这篇关于访问组中的前n个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！