<p>以下是最简单的可能示例，但任何解决方案都应能够扩展到所需的n个顶部结果：</p> <p>给定如下表格，其中包含人员、组和年龄列，如何获取每个组中年龄最大的2个人？（组内的并列情况不应产生更多结果，而是按字母顺序给出前2个）</p> <pre> +--------+-------+-----+ | Person | Group | Age | +--------+-------+-----+ | Bob | 1 | 32 | | Jill | 1 | 34 | | Shawn | 1 | 42 | | Jake | 2 | 29 | | Paul | 2 | 36 | | Laura | 2 | 39 | +--------+-------+-----+ </pre> <p>期望的结果集：</p> <pre> +--------+-------+-----+ | Shawn | 1 | 42 | | Jill | 1 | 34 | | Laura | 2 | 39 | | Paul | 2 | 36 | +--------+-------+-----+ </pre> <hr> <p><strong>注意：</strong>这个问题是基于之前的一个问题——获取每个组的最大值的记录的SQL结果 - 用于获取每个组的单个顶部行，并且从@Bohemian获得了一个很好的MySQL特定的答案：</p> <pre class="brush:php;toolbar:false;">select * from (select * from mytable order by `Group`, Age desc, Person) x group by `Group`</pre> <p>很想能够在此基础上继续构建，但我看不出如何做到。</p>

SELECT
    person,
    groupname,
    age
FROM
(
    SELECT
        person,
        groupname,
        age,
        @rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
        @prev := groupname
    FROM mytable
    JOIN (SELECT @prev := NULL, @rn := 0) AS vars
    ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2

(
  select *
  from mytable 
  where `group` = 1
  order by age desc
  LIMIT 2
)
UNION ALL
(
  select *
  from mytable 
  where `group` = 2
  order by age desc
  LIMIT 2
)

select person, `group`, age
from 
(
   select person, `group`, age,
      (@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number 
  from test t
  CROSS JOIN (select @num:=0, @group:=null) c
  order by `Group`, Age desc, person
) as x 
where x.row_number <= 2;