我有一张桌子:
表格1
Type Attribute Value Count
Fruit Apple Sweet 1772
Fruit Apple Sour 1021
Fruit Apple Sweetest 930
Fruit Apple Sweetest 930
Fruit Orange Sweetest 200
Fruit Orange Sour 190
Fruit Orange Sweetest 160
Fruit Orange Sweetest 140
我需要基于类型,属性和计数的前3行。
因此,输出应为:
Type Attribute Value Count
Fruit Apple Sweet 1772
Fruit Apple Sour 1021
Fruit Apple Sweetest 930
Fruit Orange Sweetest 200
Fruit Orange Sour 190
Fruit Orange Sweetest 160
如何获取每种类型,属性和计数的前三行?
@GordonLinoff和@LukaszSzozda的其他答案是基于原始帖子,而不是基于OP随后添加的说明。基于原始帖子的SQL Fiddle,在@Gordon的答案中使用SQL(基本上与@Lukasz的答案相同,因为他们在澄清之前在同一时间发布了答案)每Apple
行返回4行,每行返回4行Orange
:
FOOD_TYPE ATTRIBUTE VALUE CNT SEQNUM
Fruit Apple Sour 1021 1
Fruit Apple Sweet 1772 1
Fruit Apple Sweetest 930 1
Fruit Apple Sweetest 930 2
Fruit Orange Sour 190 1
Fruit Orange Sweetest 200 1
Fruit Orange Sweetest 160 2
Fruit Orange Sweetest 140 3
修改后的SQL在这里
select t.*
from (select Food.*,
row_number() over (partition by food_type, attribute order by cnt desc) as seqnum
from Food
) t
where seqnum <= 3;
返回期望的结果:
FOOD_TYPE ATTRIBUTE VALUE CNT SEQNUM
Fruit Apple Sweet 1772 1
Fruit Apple Sour 1021 2
Fruit Apple Sweetest 930 3
Fruit Orange Sweetest 200 1
Fruit Orange Sour 190 2
Fruit Orange Sweetest 160 3
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句