有时我们在处理数据库里 record 较多的 table 时,想筛选同时满足1或1个以上条件后按特定规则排序后的第一个数据,怎么做呢?

比如我们有如下内容,我们想输出  the highest Inventory of the top most 2 categories.

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

 

即得到:

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

 

以下这段代码是完美解决方案,如果想增加筛选条件,只需要再 Group By 条件下增加相关内容的选择。这里使用的是 INNER JOIN clause 来规避可能出现的外部干扰。

 

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(*) <= 2ORDER BY a.year, a.category, COUNT(*) DESC