为表中的每个组选择前 N 行 [英] selecting top N rows for each group in a table

问题描述

我面临一个非常常见的问题，即为表中的每个组选择前 N 行".

I am facing a very common issue regarding "Selecting top N rows for each group in a table".

考虑一个包含 id、name、hair_colour、score 列的表格.

Consider a table with id, name, hair_colour, score columns.

我想要一个结果集，对于每种头发颜色，都可以得到前 3 名得分手的名字.

I want a resultset such that, for each hair colour, get me top 3 scorer names.

为了解决这个问题，我在Rick Osborne 的博客文章sql-getting-top-n-rows-for-a-grouped-query"

To solve this i got exactly what i need on Rick Osborne's blogpost "sql-getting-top-n-rows-for-a-grouped-query"

当我的分数相等时，该解决方案无法按预期工作.

That solution doesn't work as expected when my scores are equal.

在上面的例子中，结果如下.

In above example the result as follow.

 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)，那么黑发"类型的头发的 ranknum 将是 2,2,3.

Consider the row 4 Deborah Brunette 9 2. If this also has same score (10) same as Sarah, then ranknum will be 2,2,3 for "Brunette" type of hair.

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

What's the solution to this?

推荐答案

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

If you're using SQL Server 2005 or newer, you can use the ranking functions and a CTE to achieve this:

;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，以此类推

This CTE will "partition" your data by the value of the hair column, and each partition is then order by score (descending) and gets a row number; the highest score for each partition is 1, then 2 etc.

因此，如果您想获得每组的前 3 名，请仅从 CTE 中选择 RowNum 为 3 或更少 (1, 2, 3) 的那些行 --> 就这样！

So if you want to the TOP 3 of each group, select only those rows from the CTE that have a RowNum of 3 or less (1, 2, 3) --> there you go!

这篇关于为表中的每个组选择前 N 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！