mysql - 根据比赛表中的胜率获取排名/排名

Question

我有一个玩家表和MatchUps表。 MatchUps 表具有一个 winner_id 列和一个 loser_id 列。我可以根据一次查询的胜率获得排名/排名吗？如果我能为每个玩家返回这样的东西，那就太好了。

PlayerName - winCount - lossCount - winPercentage

我是一个轻量级的 SQL 人员，所以我尝试过的东西还没有接近。我基本上只能获取每个玩家的获胜次数，仅此而已。

这是一个玩家表示例:

+----+-------------------------------------+
| id | name                                |
+----+-------------------------------------+
|  1 | Iron Man                            |
|  2 | Gaurdians Of The Galaxy             |
|  3 | Batman Begins                       |
|  4 | X-Men                               |
|  5 | Captain America: The First Avenger  |
|  6 | Superman II                         |
|  7 | Captain America: The Winter Soldier |
|  8 | X2: X-Men United                    |
|  9 | X-Men: First Class                  |
| 10 | Superman: The Movie                 |
| 11 | Batman                              |
| 12 | The Avengers                        |
| 13 | The Incredibles                     |
| 14 | The Dark Knight                     |
+----+-------------------------------------+

这是一个示例匹配表:

+----+-----------+----------+
| id | winner_id | loser_id |
+----+-----------+----------+
|  1 |         5 |        6 |
|  2 |         2 |        9 |
|  3 |         1 |        5 |
|  4 |         1 |        6 |
|  5 |         4 |       13 |
|  6 |         1 |       13 |
|  7 |         1 |        2 |
|  8 |         1 |        9 |
|  9 |         3 |        8 |
| 10 |         2 |        8 |
| 11 |         1 |        8 |
| 12 |         1 |       12 |
| 13 |         2 |       10 |
| 14 |         1 |       10 |
| 15 |         2 |        4 |
| 16 |         1 |        4 |
| 17 |         2 |       13 |
| 18 |         3 |       11 |
| 19 |         2 |        3 |
| 20 |         1 |        3 |
+----+-----------+----------+

Answer 1

这是执行此操作的一种方法:

fiddle : http://sqlfiddle.com/#!9/731d6/1/0

select  p.name as playername,
        x.wincount,
        x.losscount,
        case when x.wincount = 0 then 0
             else x.wincount / x.total_games
             end as winpercentage
from    (   select  player_id,
                    sum(case when outcome = 'W' then 1 else 0 end) as wincount,
                    sum(case when outcome = 'L' then 1 else 0 end) as losscount,
                    count(*) as total_games
            from    (
                        select  winner_id as player_id,
                                'W' as outcome
                        from    matchups
                        union all
                        select  loser_id,
                                'L'
                        from    matchups
                    ) x
            group by player_id
        ) x
   join players p 
     on x.player_id = p.id