对前10个值求和 [英] Sum Top 10 Values

问题描述

我已经搜索过，而且我知道以前已经有人问过这个问题，但是我一直在努力寻找自己能做/不能做的事情.

I’ve searched and I know this has been asked before but I am struggling to get my head around what I can / can’t do.

我的自行车俱乐部每次骑手参加比赛时都会记录比赛结果.每个结果都将获得积分-第1点为50分，第2点为49分，等等.

My cycling club records race results each time a rider has entered a race. Each result is awarded points - 50 for 1st, 49 for 2nd etc.

所以桌子看起来像

resultid(pk) | riderid(fk) | leaguepts
     1              1            50
     2              2            49
     3              3            48
     4              1            50
     5              2            42
     6              3            50
     7              4            30
...etc

我正在尝试从结果表中提取为每个 riderid 所奖励的前10分.

I am trying to extract the sum of top 10 points awarded for each riderid from the results table.

(实际数据库有点复杂，其中包含一个有关车手姓名/车手ID的表以及一个比赛表，因此我们可以显示每场比赛的结果等，但我只想让基本的联赛表查询首先生效全部)

(the actual database is a bit more complicated with a table for rider name / rider id and also a race table so we can display the results of each race etc but I just want to get the basic league table query working first of all)

所以我想提取每个骑手的前10个最佳成绩的总和.然后在降序的联赛表中显示每个车手的得分.

So I want to extract the sum of the top 10 best scores for each rider. Then display each riders score, in a descending league table.

到目前为止，我仅在使用UNION ALL方面取得了成功，例如

So far I’ve only had success using UNION ALL e.g.

SELECT sum(points) AS pts from
(
    SELECT points from `results`
    WHERE riderid = 1
    ORDER BY points DESC
    LIMIT 10
) as riderpts
UNION ALL
SELECT sum(points) AS pts from
(
    SELECT points from `results`
    WHERE riderid = 2
    ORDER BY points DESC
    LIMIT 10
) as riderpts
ORDER BY pts DESC

但是可能有多达90多个骑手至少注册了一个分数，所以这个查询可能会很大.

But there could be up to 90-odd riders who have registered at least one score so this query could get very big.

我发现它看起来应该对我有用，但不起作用.对MySQL中的前5个值求和我更改了表的列名，但是似乎是所有结果的总和，而不是每个骑手的前10名.

I found this which looks like it should work for me but doesn't. Sum top 5 values in MySQL I changed the column names for my table but it seems to sum all results, not the top 10 for each rider.

或者，我可以对每个骑手ID发出查询.我猜不好吗?

Alternatively I could just issue a query for each rider id. Not good I guess?

子查询是一个问题，因为我不能限制内部查询吗?

Subquerying is a problem because I can't limit on the inner query?

运行作业(手动或cron)来定期更新联赛表并仅显示表结果吗?

Run a job (manual or cron) to update the league table periodically and just display the table results?

编辑(不确定这是否是正确的礼节还是我应该开始新的话题?).戈登回答了以下问题，但与此同时，我尝试使用下面的链接之一自己解决这个问题.我可以通过以下查询获得返回每个骑手得分最高的10分的结果

Edit (not sure if this is the correct etiquette or I should start a new thread?). Gordon answered the question below but in the meantime I tried to work this out for myself using one of the links below. I could get results that returned the top 10 scores for each rider with the query below

set @riderid = '';
set @riderrow  = 1;

select riderid, leaguepts, row_number
from
(
    select
    riderid,
    leaguepts,
    @riderrow := if(@riderid = riderid, @riderrow + 1, 1) as row_number,
    @riderid := riderid as dummy
    from wp_tt_results order by riderid, leaguepts desc
) as x where x.row_number <= 10;

但是我看不到接下来要做什么才能获得每个 riderid 的前10个结果之和?

BUT I can't see what I would need to do next to get the sum of top 10 results per riderid?

推荐答案

在MySQL中，最简单的方法可能是使用变量:

In MySQL, the easiest way to do this is probably to use variables:

SELECT riderid, sum(points)
FROM (SELECT r.*,
             (@rn := if(@r = riderid, @rn + 1,
                        if(@r := riderid, 1, 1)
                       )
             ) as seqnum
      FROM results r CROSS JOIN
           (SELECT @r := 0, @rn := 0) as wnw
      ORDER BY riderid, points DESC
     ) r
WHERE seqnum <= 10
GROUP BY riderid;

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