mysql - 如何从 MySQL 高分板中获取每场比赛的前 5 名球员？

Question

我正在尝试设置一个高分板以显示在我的街机游戏室中。我的目标是显示表 GAME 中每场比赛的前 5 名玩家。

我创建了三个表:

table 上游戏:

+----------+--------------+
| id_game | name_game |
+----------+--------------+
| 1 | PAC MAN | 
| 2 | GALAXIAN |
| 3 | XEVIOUS |

桌面播放器:

+----------+---------------+
| id_player | name_player |
+----------+---------------+
| 1 | BRUNO | 
| 2 | STEVE |

表格得分:

+----------+----------+-----------+------+------+
| id_score | id_game | id_player | date | score | 
+----------+----------+-----------+------+------+
| 1 | 1 | 1 | 01/10/15 | 230234 |
| 2 | 1 | 2 | 04/10/15 | 120234 |
| 3 | 1 | 1 | 03/10/15 | 440224 |
| 4 | 1 | 1 | 06/10/15 | 200000 |
| 5 | 1 | 2 | 09/10/15 | 330233 |
| 6 | 1 | 1 | 01/10/15 | 510000 |
| 7 | 2 | 1 | 01/10/15 | 730874 |
| 8 | 2 | 2 | 01/10/15 | 990900 |
| 9 | 3 | 1 | 01/10/15 | 444000 |

我需要一些帮助来构建 sql 代码以生成如下内容:

吃 bean 人

1. BRUNO 510000 PTS 01/10/15
2. STEVE 330233 PTS 09/10/15
3. XXXXX 230233 PTS 02/10/14
4. YYYYY 130233 PTS 06/10/15
5. ZZZZZ 030233 PTS 10/10/13

银河系

1. STEVE 990900 PTS 01/10/15
2. BRUNO 730874 PTS 01/10/15
3. XXXXX 230233 PTS 02/10/14
4. YYYYY 130233 PTS 06/10/15
5. ZZZZZ 030233 PTS 10/10/13

如果同一名球员在同一场比赛中得分超过一个，则只应显示他/她的最好成绩。你能帮我设置执行此操作的代码吗？

Answer 1

SQL fiddle

SELECT MAX(s.score) AS score, p.name_player, g.name_game
FROM GAME g
JOIN SCORE s ON s.id_game = g.id_game
JOIN PLAYER p ON p.id_player = s.id_player
GROUP BY p.id_player, g.id_game
ORDER BY g.id_game, score

好吧，我仍然对你上面的查询有疑问:-(
查询很好地显示了每个唯一玩家的最佳分数，但关联的日期不正确。 “日期”字段仍然给出每个玩家完成的第一个得分的第一个日期。
让我来说明一下。
我只是将 DATE 值添加到查询的第一行，它给出:

 SELECT MAX(s.score) AS score, p.name_player, g.name_game, date
FROM GAME g
JOIN SCORE s ON s.id_game = g.id_game
JOIN PLAYER p ON p.id_player = s.id_player
GROUP BY p.id_player, g.id_game
ORDER BY g.id_game, score


+----------+----------+-----------+------+------+
| id_score | id_game | id_player | date | score | 
+----------+----------+-----------+------+------+
| 1 | 1 | 1 | 01/10/12 | 230234 |
| 2 | 1 | 1 | 04/10/13 | 120234 |
| 3 | 1 | 1 | 03/10/14 | 440224 |

您的查询将返回:440224 1 1 2012 年 1 月 10 日

但我期待这个:440224 1 1 03/10/14

我试图在此处添加 max(date)，但我想显然还有更复杂的事情要做？