试试这个:

SELECT h.year, h.id, h.rate 
FROM (SELECT h.year, h.id, h.rate, IF(@lastid = (@lastid:=h.id), @index:=@index+1, @index:=0) indx 
      FROM (SELECT h.year, h.id, h.rate 
            FROM h
            WHERE h.year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2)
            GROUP BY id, h.year
            ORDER BY id, rate DESC
            ) h, (SELECT @lastid:='', @index:=0) AS a
    ) h 
WHERE h.indx <= 5;

不，你不能任意地限制子查询(你可以在较新的mysql中有限地这样做，但不能每组5个结果)。

这是一个分组最大类型查询，在SQL中执行起来并不简单。在某些情况下，有很多更有效的方法来解决这个问题，但对于一般的top-n，你会想看看Bill对前面一个类似问题的答案。

与此问题的大多数解决方案一样，如果有多行具有相同的速率值，它可以返回超过5行，因此您可能仍然需要大量的后处理来检查这一点。

你想找出每个组的前n行。这个答案使用与OP不同的示例数据提供了一个通用的解决方案。

在MySQL 8或更高版本中，您可以根据top 5的确切定义使用ROW_NUMBER, RANK或DENSE_RANK函数。下面是这些函数根据值降序排序生成的数字。注意领带是如何处理的:

一旦你选择了函数，就像这样使用它:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC) AS n
    FROM t
) AS x
WHERE n <= 5

DB < >小提琴

在MySQL 5。X，你可以使用穷人的排名超过分区，以达到预期的结果:外部连接表本身和每一行，计算它之前的行数(例如，前一行可以是一个较高的值)。

下面将产生类似RANK函数的结果:

SELECT t.pkid, t.catid, t.value, COUNT(b.value) + 1 AS rank
FROM t
LEFT JOIN t AS b ON b.catid = t.catid AND b.value > t.value
GROUP BY t.pkid, t.catid, t.value
HAVING COUNT(b.value) + 1 <= 5
ORDER BY t.catid, t.value DESC, t.pkid

进行以下更改以产生与DENSE_RANK函数类似的结果:

COUNT(DISTINCT b.value)

或进行以下更改，以产生类似于ROW_NUMBER函数的结果:

ON b.catid = t.catid AND (b.value > t.value OR b.value = t.value AND b.pkid < t.pkid)

DB < >小提琴

这需要一系列子查询对值进行排序、限制，然后在分组时执行求和

@Rnk:=0;
@N:=2;
select
  c.id,
  sum(c.val)
from (
select
  b.id,
  b.bal
from (
select   
  if(@last_id=id,@Rnk+1,1) as Rnk,
  a.id,
  a.val,
  @last_id=id,
from (   
select 
  id,
  val 
from list
order by id,val desc) as a) as b
where b.rnk < @N) as c
group by c.id;

试试这个:

SELECT h.year, h.id, h.rate 
FROM (SELECT h.year, h.id, h.rate, IF(@lastid = (@lastid:=h.id), @index:=@index+1, @index:=0) indx 
      FROM (SELECT h.year, h.id, h.rate 
            FROM h
            WHERE h.year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2)
            GROUP BY id, h.year
            ORDER BY id, rate DESC
            ) h, (SELECT @lastid:='', @index:=0) AS a
    ) h 
WHERE h.indx <= 5;

下面的post: sql:每组选择前N条记录描述了在没有子查询的情况下实现这一目标的复杂方法。

它改进了这里提供的其他解决方案:

在单个查询中执行所有操作 能够正确地利用索引 避免子查询，众所周知，在MySQL中会产生糟糕的执行计划

然而，它并不漂亮。如果在MySQL中启用窗口函数(又名分析函数)，就可以实现一个很好的解决方案——但它们没有。 文章中使用的技巧利用了GROUP_CONCAT，它有时被描述为“穷人的MySQL窗口函数”。