mysql - 调整MySQL排名查询

Question

我正在尝试从我拥有的比赛表中获取排名列表。 matches 表中的每一行都代表一场比赛，包括赢家和输家。排名应基于特定玩家累积的胜利次数。我目前有一个根据获胜次数对玩家进行排名的查询，但它排除了 0 次获胜的玩家。

如何调整我的查询，以便结果中包含 0 场胜利的玩家？我能否将损失考虑在内，以便根据损失的数量对 0 胜的玩家进行排名？

所有相关信息如下。谢谢。

匹配表:

winner   |   loser   |    winner_score   |   loser_score
--------------------------------------------------------
Jason    |   Alex    |         15        |       13
Bill     |   Jason   |         14        |       11
Alex     |   Bill    |         12        |        6
Jason    |   Scott   |         13        |        8

我当前的查询:

SELECT t1.player, 
   (SELECT Count(*) 
    FROM   (SELECT Count(*) AS wins, 
                   winner   AS player 
            FROM   matches 
            GROUP  BY winner 
            ORDER  BY wins DESC) t2 
    WHERE  t2.wins > t1.wins) 
   + 1 AS rank, 
   t1.wins 
FROM   (SELECT Count(*) AS wins, 
           winner   AS player 
    FROM   matches 
    GROUP  BY winner 
    ORDER  BY wins DESC) t1 

我目前的结果:

player   |   rank  |    wins
------------------------------
Jason    |    1    |     2
Bill     |    2    |     1
Alex     |    2    |     1

我想要的结果:

player   |   rank  |    wins   |   losses
---------------------------------------------
Jason    |    1    |     2     |     1
Bill     |    2    |     1     |     1
Alex     |    2    |     1     |     1
Scott    |    3    |     0     |     1

Answer 1

看似复杂，其实不然:)

• 看到它在 sqlfiddle 中实时运行

---

select player, wins, losses, rank from (

    select ssq.*, 
    @rank := if(@prev_win = wins, @rank, @rank + 1) as rank,
    @prev_win := wins
    from (

        select
        player,
        sum(won=1) as wins,
        sum(won=0) as losses
        from (

            select
            winner as player,
            1 as won
            from
            Table1
            union all
            select
            loser as player,
            0 as won
            from 
            Table1

        ) sq
        group by player

    ) ssq
    , (select @rank := 0, @prev_win := null) var_init
    order by wins desc, wins-losses asc

) sssq