我正在面对“选择表中每个组的前N行”的一个很常见的问题.
考虑一个id,name,hair_colour,score列的表.
我想要一个结果集,这样,对于每个头发的颜色,得到我的前3名得分手的名字.
为了解决这个问题,我得到了我所需要的Rick Osborne’s blogpost “sql-getting-top-n-rows-for-a-grouped-query”
当我的分数相等时,该解决方案无法预期.
在上面的例子中,结果如下.
id name hair score ranknum --------------------------------- 12 Kit Blonde 10 1 9 Becca Blonde 9 2 8 Katie Blonde 8 3 3 Sarah Brunette 10 1 4 Deborah Brunette 9 2 - ------- - - > if 1 Kim Brunette 8 3
考虑第4行Deborah Brunette 9 2.如果这也与Sarah相同(10),那么对于“Brunette”型头发来说,ranknum将是2,2,3.
这是什么解决方案？
解决方法
如果您使用的是sql Server 2005或更高版本,则可以使用排名函数和CTE来实现：
;WITH HairColors AS (SELECT id,hair,score,ROW_NUMBER() OVER(PARTITION BY hair ORDER BY score DESC) as 'RowNum' ) SELECT id,score FROM HairColors WHERE RowNum <= 3
该CTE将根据头发列的值“分割”您的数据,然后每个分区按顺序排列(降序)并获取行号;每个分区的最高分数为1,然后为2等.
所以如果你想要每个组的TOP 3,只选择Cate中行RowNum为3或更少(1,3) – >的行.你去吧
