mysql - 多项目锦标赛排名(任意数量的参赛作品)

Question

假设您有一个多项目竞赛，参赛者可以任意次数地尝试任何项目。 (很奇怪，我知道。)

如何为每个事件拉出所需玩家的最佳时间，并为其分配一个位置？ (第一第二第三...)

Data example:               Desired output:

Name | Event | Score        Name | Event | Score | Rank
--------------------        ----------------------------
Bob      1      50          Given input: "Bob"
Bob      1     100          Bob      1     100      1   
Bob      2      75          Bob      2      75      3
Bob      3      80          Bob      3      80      2
Bob      3      65      
                            Given input: "Jill"
Jill     2      75          Jill     2      90      1
Jill     2      90          Jill     3      60      3
Jill     3      60
                            Given input: "Chris"
Chris    1      70          Chris    1      70      2
Chris    2      50          Chris    2      85      2
Chris    2      85          Chris    3     100      1
Chris    3     100

这是我之前问题的基础: Multi-event tournament standings

我感觉对这个问题的理解好多了(谢谢!)，但我无法弥合这个版本问题的差距。

我有 SQL 5.x，所以我不能使用像 Rank() 这样的东西。这也将处理成千上万的分数。

Answer 1

可以通过此查询获得所需的输出:

select
IF(event is NULL, CONCAT('Given input: "', name,'"'), name) as name,
IF(event is NULL, '', event) as event,
IF(event is NULL, '', max(score)) as score,
IF(event is NULL, '', (
  select count(s2.name) + 1
  from (
          select name, max(score) as score
          from scores es
          where es.event = s.event
          group by es.name
          order by score desc
        ) s2
  where s2.score > max(s.score)
)) as `rank`

from scores s
group by name, event with rollup
having name is not NULL
order by name, event;

和输出(如果在 mysql cli 中运行查询):

+----------------------+-------+-------+------+
| name                 | event | score | rank |
+----------------------+-------+-------+------+
| Given input: "Bob"   |       |       |      |
| Bob                  | 1     | 100   | 1    |
| Bob                  | 2     | 75    | 3    |
| Bob                  | 3     | 80    | 2    |
| Given input: "Chris" |       |       |      |
| Chris                | 1     | 70    | 2    |
| Chris                | 2     | 85    | 2    |
| Chris                | 3     | 100   | 1    |
| Given input: "Jill"  |       |       |      |
| Jill                 | 2     | 90    | 1    |
| Jill                 | 3     | 60    | 3    |
+----------------------+-------+-------+------+
11 rows in set, 3 warnings (0.00 sec)

应该适用于任何 Mysql 5。