总结 MySQL 中的前 5 个值

2022-01-09 00:00:00 sum group-by select subquery mysql

我有一个 MySQL 表，我在其中存储来自赛车锦标赛的结果，因此每一行都包含 - 在其他数据中 - 每个车手在某场比赛中的位置.我想得到某个驱动程序的前 5 名的总和(例如，如果驱动程序的最佳位置是 1、2、2、4、5，我希望 MySQL 返回 14).我想要做的是:

I have a MySQL table where I store results from a racing championship, so that every rows contains -among other data- every driver's position in a certain race. I want to get the sum of a certain driver's top 5 placings (for instance, if a driver's best positions were 1,2,2,4,5, I'd like MySQL to return 14). What I'm trying to do is this:

SELECT driver, SUM(position)
FROM results
WHERE (race, season, position) IN
   (SELECT race, season, position
    FROM results
    WHERE driver = "Vettel"
    ORDER BY position ASC
    LIMIT 5) 
AND driver = "Vettel"

当然，(种族、季节、位置)是结果"表的主键.现在，问题是我不能真正让它工作，因为 MySQL 还不支持在内部子查询中使用 LIMIT.你会怎么做呢?

With (race, season, position) being a primary key for the "results" table, of course. Now, the problem is that I can't really get this to work , as MySQL doesn't yet support the use of LIMIT in inner subqueries. How would you do this?

为了获得额外的奖励 - 有没有办法通过单个查询获得每个驱动程序的前 5 个结果的总和，而不是单个驱动程序?

And for extra credit - instead of a single driver, is there a way to get the sum of the top 5 results of every driver with a single query?

推荐答案

试试这个:

SELECT driver, SUM(`position`)
FROM (SELECT driver, race, season, `position`, 
             IF(@lastDriver=(@lastDriver:=driver), @auto:=@auto+1, @auto:=1) indx 
      FROM results, (SELECT @lastDriver:=0, @auto:=1) A 
      ORDER BY driver, `position`) AS A  
WHERE indx <= 5 
GROUP BY driver ;

相关文章