在SQL中，如何为每个组选择前两行 [英] In SQL, how to select the top 2 rows for each group

问题描述

我有一个表如下：

I have a table as following:

NAME    SCORE
-----------------
willy       1
willy       2
willy       3
zoe         4
zoe         5
zoe         6

以下是样本

组的聚合函数只能让我获得最高分为每个名称。
我想查询每个 name 的最高2分，我应该怎么做？

The aggregation function for group by only allow me to get the highest score for each name. I would like to make a query to get the highest 2 score for each name, how should I do?

我的预期产出是

My expected output is

NAME    SCORE
-----------------
willy       2
willy       3
zoe         5
zoe         6

推荐答案

SELECT *
FROM   test s
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    test  f
            WHERE f.name = s.name AND 
                  f.score >= s.score
        ) <= 2

• SQLFiddle Demo


• SQLFiddle Demo

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