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

Question

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

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

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

为了解决这个问题，我得到了我所需要的 Rick Osborne 的博文“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)，那么对于“黑发”类型的头发，ranknum 将是 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，依此类推。

因此，如果您想要每个组的前 3 名，请仅从 CTE 中选择那些 RowNum 等于或小于 3 的行（1、 2, 3) -->就这样吧！

Answer 2

该算法得出排名的方式是计算叉积中分数等于或大于相关女孩的行数，以生成排名。因此，在您讨论的问题案例中，莎拉的网格看起来

a.name | a.score | b.name  | b.score
-------+---------+---------+--------
Sarah  | 9       | Sarah   | 9
Sarah  | 9       | Deborah | 9

与黛博拉的网格相似，这就是为什么两个女孩在这里都获得 2 的排名。

问题是，当出现平局时，由于此计数，所有女孩都会采用平局范围内的最低值，而您希望她们取最高值。我认为一个简单的改变可以解决这个问题：

不要使用大于或等于比较，而是使用严格的大于比较来计算严格更好的女孩的数量。然后，再加一，你就得到了你的排名（这将酌情处理平局）。因此，内部选择将是：

SELECT a.id, COUNT(*) + 1 AS ranknum
FROM girl AS a
  INNER JOIN girl AS b ON (a.hair = b.hair) AND (a.score < b.score)
GROUP BY a.id
HAVING COUNT(*) <= 3

任何人都可以看到这种方法中我没有注意到的任何问题吗？

Answer 3

使用此复合选择可以正确处理 OP 问题

SELECT g.* FROM girls as g
WHERE g.score > IFNULL( (SELECT g2.score FROM girls as g2
                WHERE g.hair=g2.hair ORDER BY g2.score DESC LIMIT 3,1), 0)

请注意，您需要在此处使用 IFNULL 来处理表 girls 对于某种类型的 hair 的行数较少的情况，然后我们希望看到在sql答案中（在OP情况下是3个项目）。