用于计算谁的比赛胜利数最高的SQL查询

Question

我正在创建一个简单的数据库，它将允许我跟踪斯诺克的结果，在球员之间产生头对头的结果。目前我有3个表：(球员，设备，结果)

PlayerID    PlayerName
1           Michael Abraham
2           Ben Mullen
3           Mark Crozier

FixtureID   Date                 TableNo    Group
1           07/12/2015 19:00:00  12         0
2           08/12/2015 12:00:00  9          0

ResultID    FixtureID   PlayerID    FramesWon
1           1           1           3
2           1           3           1
3           2           1           2
4           2           3           5

正如您在结果表中看到的，Player1已经与Player3进行了两次比赛，Player1以3-1赢得了第一场比赛，Player3以5-2赢得了第二场比赛。在这种情况下，预期输出应为：

PlayerID    MatchesWon
1           1
3           1

Answer 1

我同意使用窗口函数是最好的方法(例如SQL Server )，使用直接的SQL方法是可能的(假设在“装备”中获胜最多的是比赛胜利者)。

    SELECT PlayerId, FixtureID, Count(*) As MatchesWon
    FROM Result r 
    WHERE r.Frameswon = (SELECT MAX(frameswon) FROM Result r2
                  WHERE 
                  r.FixtureId = r2.FixtureId)
    GROUP BY PlayerID,FixtureId

或者如果可以省略fixtureId，只过滤两个播放器，就像这样。有了上面给出的数据，应该可以得到样本结果。

    SELECT PlayerId, MatchesWon 
    FROM
    (
    SELECT FixtureID,PlayerId, Count(*) As MatchesWon
    FROM Result r 
    WHERE r.Frameswon = (SELECT max(frameswon) FROM Result r2
                  WHERE 
                  r.FixtureId = r2.FixtureId)

   GROUP BY FixtureId,PlayerID
   ) s
   WHERE 
   PlayerID IN (1,3)

Answer 2

也许这对你来说是可行的：

select playerid, count(*) as matcheswon
from result as r1
where frameswon = 
 (
   select max(frameswon)
   from result as r2
   where r2.fixtureid = r1.fixtureid
   )
group by playerid

在这里很麻烦：http://sqlfiddle.com/#!9/60821/2

Answer 3

这是您可以尝试的替代方案。

SELECT r.PlayerID, COUNT(r.PlayerID)
FROM (
    SELECT FixtureID, MAX(FramesWon) AS FramesWon
    FROM `result`
    GROUP BY FixtureID    
) win
INNER JOIN result r ON win.FixtureID = r.FixtureID AND win.FramesWon = r.FramesWon
GROUP By r.PlayerID