一组一组的前2个结果?

I have a table "record: id,name,score" and would like to query for the 2 highest scores per each name. I am using group by to get the highest score as :

我有一个表“记录:id,name,score”，我想查询每个名字的2个最高分数。我使用group by获得最高分数为:

select name,max(score)as score from record group by name order by score

But I think its not possible to get the 2 highest scores using group by, how do I get them ?

但是我认为不可能通过小组获得2分的高分，我怎么才能得到呢?

3 个解决方案

#1

What you need is a ranking function which MySQL does not natively support at the moment. However, you can simulate it like so:

您需要的是一个目前MySQL不支持的排名函数。但是，可以这样模拟:

Select name, score, rnk
From  (
      Select name, score
        , @curRank := if(@name=name, if(@score=score, @curRank, @curRank + 1), 1) As rnk
        , @name := name
        , @score := score
      From  (
            Select name, score
            From record
              Cross Join ( Select @curRank := 0, @name := '', @score := -1 ) As Z1
            Order By name, score Desc
            ) as R
      ) As Z
Where rnk <= 2

SQL Fiddle

SQL小提琴

#2

 SELECT name, score FROM record R1
   WHERE (SELECT COUNT(DISTINCT score) FROM record R2
          WHERE R2.name = R1.name 
            AND R2.score >= R1.score) <= 2

Not especially performant (that is, may be kind of slow), but it should return what you're looking for.

不是特别的表现(也就是说，可能有点慢)，但是它应该返回你想要的。

#3

select TOP (2) name,max(score)as score from record group by name order by score

选择TOP (2) name,max(score)as score from record group by name order by score

EDIT: I just noticed its in mysql then

编辑:我刚注意到它在mysql中

select name,max(score)as score from record group by name order by score LIMIT 2

选择name,max(score)作为记录组的分数，按姓名顺序按分数限制2

#1