sql – 访问组中的前n个

发布时间：2020-12-12 16:26:30 所属栏目：MsSql教程来源：网络整理

导读：我有一张桌子,我需要为每个类别获得前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 我想要的输出是最顶

我有一张桌子,我需要为每个类别获得前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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!