如果您使用的是 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!