在MySQL中总结前5个值 [英] Sum top 5 values in MySQL

问题描述

我有一张MySQL表格，用于存储赛车锦标赛的成绩，以便每行都包含其他数据 - 每个驾驶员在特定比赛中的位置。我想要得到某个车手排名前5的总和（例如，如果驾驶员的最佳位置是1,2,2,4,5，我希望MySQL返回14）。我试图做的是：

$ p $ SELECT驱动程序，SUM（位置）
FROM results
WHERE（比赛，赛季，位置）IN
（选择比赛，赛季，位置
结果
WHERE司机=Vettel
按位置排列ASC
限制5）
AND driver =Vettel


随着（比赛，赛季，位置）当然，结果表的主键。现在，问题在于我无法真正做到这一点，因为MySQL尚不支持在内部子查询中使用LIMIT。你会如何做到这一点？

为了获得额外的荣誉 - 而不是单个驱动程序，是否有办法每次获得前5个结果的总和

使用单个查询的驱动程序？

解决方案

试试这个：

  SELECT driver，SUM（`position`）
 FROM（SELECT driver，race，season，`position`，
 IF（@lastDriver =（@ lastDriver ：@auto：= @ auto + 1，@auto：= 1）indx 
 FROM results，（SELECT @lastDriver：= 0，@auto：= 1）A 
 ORDER BY驱动程序，`position`）AS A 
 WHERE indx< = 5 
 GROUP BY驱动程序; 
  

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"

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?

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?

解决方案

Try this:

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 ;

这篇关于在MySQL中总结前5个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！