我有一个表格,我需要在其中获取每个类别的前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
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”子句中。
SELECT
仅当您要扩展“ 每个Category,foo,bar的TOP n ”时,才将这些列也添加到该INNER JOIN子句中。
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