mysql - 模拟mysql中的排名(无排名)有两个条件

Question

来自这个答案:Rank based on two columns

我有以下示例:

CREATE TABLE tmpPoradi 
    (`player` int, `wins` int, `diff` int)
;

INSERT INTO tmpPoradi 
    (`player`, `wins`, `diff`)
VALUES
    (1, 10, 12),
    (2, 8, 2),
    (3, 10, 10),
    (4, 8, 1),
    (5, 8, 7),
    (6, 10, 14),
    (8, 10, 10),
   (7, 12, 3)
;

并且期望的结果必须是:

+--------+------+------+------+
| player | wins | diff | rank |
+--------+------+------+------+
|      7 |   12 |    3 |    1 |
|      6 |   10 |   14 |    2 |
|      1 |   10 |   12 |    3 |
|      3 |   10 |   10 |    4 |
|      8 |   10 |   10 |    4 |
|      5 |    8 |    7 |    5 |
|      2 |    8 |    2 |    6 |
|      4 |    8 |    1 |    7 |
+--------+------+------+------+

问题是原始答案返回行数:

 SELECT player, wins, diff,rank from
 (
 SELECT player, wins, diff, @winrank := @winrank + 1 AS rank
 from tmpPoradi,(SELECT @winrank := 0) r 
 ORDER BY wins DESC,diff DESC
 )  rt
ORDER BY rank 

返回:

+---------+-------+-------+-------+
|  player |  wins |  diff |  rank |
+---------+-------+-------+-------+
|       7 |    12 |     3 |     1 |
|       6 |    10 |    14 |     2 |
|       1 |    10 |    12 |     3 |
|       3 |    10 |    10 |     4 |
|       8 |    10 |    10 |     5 |
|       5 |     8 |     7 |     6 |
|       2 |     8 |     2 |     7 |
|       4 |     8 |     1 |     8 |
+---------+-------+-------+-------+

我的条件尝试返回以下结果:(不知道为什么)

  SELECT player, wins, diff,rank from
 (
 SELECT player, wins, diff, @winrank := IF(wins = diff,@winrank,@winrank + 1) AS rank
 from tmpPoradi,(SELECT @winrank := 0) r 
 ORDER BY wins DESC,diff DESC
 )  rt
ORDER BY rank 


+---------+-------+-------+-------+
|  player |  wins |  diff |  rank |
+---------+-------+-------+-------+
|       7 |    12 |     3 |     1 |
|       6 |    10 |    14 |     2 |
|       3 |    10 |    10 |     3 |
|       8 |    10 |    10 |     3 |
|       1 |    10 |    12 |     3 |
|       5 |     8 |     7 |     4 |
|       2 |     8 |     2 |     5 |
|       4 |     8 |     1 |     6 |
+---------+-------+-------+-------+

所以问题是:

我做错了什么，如何通过胜利获得排名，然后如果胜利平局，则按差异进行子排名，然后如果差异平局，排名相同？

Answer 1

<强> SQL DEMO

  SELECT player, wins, diff, dense_rank, rank, dense_val, prev_wins, prev_diff
  FROM
     (
         SELECT player, 
                wins,                 
                diff, 
                @dense_rank := IF(wins = @prev_wins and diff = @prev_diff, @dense_rank, @dense_rank + @dense_val ) AS dense_rank,                
                @dense_val  := IF(wins = @prev_wins and diff = @prev_diff, @dense_val + 1 , 1) as dense_val,
                @rank := @rank + 1 as rank,
                @prev_wins := wins as prev_wins,
                @prev_diff := diff as prev_diff

         FROM tmpPoradi,(SELECT @dense_rank := 0, @dense_val := 1, @rank := 0, @prev_wins := 0, @prev_diff := 0) r 
         ORDER BY wins DESC,diff DESC
     )  rt
  ORDER BY rank 

输出