sql - 为表中的每个组选择前 N 行

Question

我面临一个关于“为表中的每个组选择前 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 .如果这也有与莎拉相同的分数 (10)，那么“黑发”类型的头发的等级将是 2,2,3。

解决这个问题的方法是什么？

Answer 1

如果您使用的是 SQL Server 2005 或更新版本，您可以使用排名函数和 CTE 来实现这一点:

;WITH HairColors AS
(SELECT id, name, hair, score, 
        ROW_NUMBER() OVER(PARTITION BY hair ORDER BY score DESC) as 'RowNum'
)
SELECT id, name, hair, score
FROM HairColors
WHERE RowNum <= 3

此 CTE 将按 hair 的值对您的数据进行“分区”。列，然后每个分区按分数(降序)排序并获得行号；每个分区的最高分是 1，然后是 2，以此类推。

因此，如果您想获得每组的 TOP 3，请仅从 CTE 中选择具有 RowNum 的那些行。 3 个或更少 (1, 2, 3) --> 你去吧!