mysql - 从数据库中选择多个top3

Question

我想创建一个数据库 View 。我有表格，其中包含有关人民的记录，它解决了一年中的一些逻辑示例，他们因此得到了分数。

我需要从 SQL 查询创建 View ，但我遇到了一个问题 - 我需要通过一个查询每年获取前 3 位用户。

SELECT solver, year, SUM(points) as TotalPoints
FROM solving
GROUP BY solver, year 
ORDER BY year, TotalPoints DESC

我每年都会得到按点和年份排序的求解器。我知道我需要使用 limit，但我也知道我将需要一个 Select more，但我不知道在哪里。

Answer 1

MySQL 有多种方法可以解决这个问题。也许最有效的是使用变量。以下列举了这些值:

SELECT yt.*,
       (@rn := if(@y = year, @rn + 1,
                  if(@y := year, 1, 1)
                 )
       ) as seqnum
FROM (SELECT solver, year, SUM(points) as TotalPoints
      FROM solving
      GROUP BY solver, year
     ) yt CROSS JOIN
     (SELECT @y := 0, @rn := 0) vars
ORDER BY year, TotalPoints DESC;

然后使用它作为子查询来获取前三个:

SELECT yt.*
FROM (SELECT yt.*,
             (@rn := if(@y = year, @rn + 1,
                        if(@y := year, 1, 1)
                       )
             ) as seqnum
      FROM (SELECT solver, year, SUM(points) as TotalPoints
            FROM solving
            GROUP BY solver, year
           ) yt CROSS JOIN
           (SELECT @y := 0, @rn := 0) vars
      ORDER BY year, TotalPoints DESC
     ) yt
WHERE seqnum <= 3;