mysql - 从具有 3 个联接的 1 个 MySQL 表获取结果并添加新行

Question

我有这样的 table :

<-T1->

name    rnk     rce     pts     elo

p1      1       1       15      1021
p2      2       1       12      1010
p3      3       1       8       960
p1      2       2       12      1030
p2      1       2       15      1020
p3      3       2       8       975
p1      1       3       15      1030
p2      2       3       12      1025
p3      3       3       8       970
p1      2       4       12      1038
p2      1       4       15      1028
p3      3       4       8       956

结果应该如下所示:

<-result->

name    wins    gesPts      elo

p1      2       54          1038
p2      2       54          1028
p3      0       32          956

到目前为止我所拥有的东西本身就很好用

• 我只计算每个玩家的排名 1.
• 我总结了每个玩家的所有分数
• 我得到了最后一场比赛的 elo

但我无法在一个表结果中得到它

SELECT T1.name,T1.rnk,T1.pts,T1.elo
FROM T1
JOIN
    (SELECT name,MAX(rce) AS lastRace
    FROM T1
    GROUP BY name) maxR

    ON (T1.rce= maxR.lastRace)

JOIN 
(SELECT name,SUM(pts) AS gesPts
    FROM T1
    GROUP BY name) gP

    ON (T1.name = gP.name)

JOIN
    (SELECT name,COUNT(rnk) as wins
    FROM T1
    WHERE rnk = 1
    GROUP BY name) Win

    ON (T1.name= Win.name)
GROUP BY name

我尝试了很多方法，但还是没能做到正确。

Answer 1

尝试使用条件聚合。我认为这可能有效(至少根据您的示例数据它给出了正确的结果):

select 
    t1.name, 
    count(case when rnk = 1 then rnk end) as wins,  -- could be written as sum(rnk=1)
    sum(pts) as gespts, 
    max(case when lastrace is not null then elo end) as elo 
from t1 left join (
    select name, max(rce) as lastrace
    from t1
    group by name
) maxr on t1.rce = maxr.lastrace and t1.name = maxr.name
group by t1.name;

Sample SQL Fiddle