我有一张桌子,我需要为每个类别获得前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
解决方法
这应该适合你.如果它不符合您的要求,请回复您的需求.
你最初的愿望就是拥有25,所以你只需将最后一个句子修改为HAVING COUNT(*)< = 25
你最初的愿望就是拥有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’子句中.
只有当你想扩展“每个类别的顶部n,foo,bar”时,你才会将这些列添加到INNER JOIN子句中.
--show the top 2 items for each category and year.
SELECT a.item,a.year,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.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.year,COUNT(*) DESC
