如何用SQL查询各游戏的最高玩家分数及关联信息

查询每个游戏的最高分数，同时获取以下信息：

• 游戏名称
• 游戏类别
• 取得该最高分的玩家标识（PlayerCode）
• 分数记录时间
• 分数值

现有表结构如下：

CREATE TABLE Player(
PlayerId INT,
Date DATE,
PlayerCode CHAR(3),
PRIMARY KEY (PlayerId)
);

CREATE TABLE Game(
GameId INT,
Name VARCHAR(128),
Description VARCHAR(256),
Launched DATE,
Category ENUM('Adventure', 'Action', 'RPG', 'Simulation', 'Sports', 'Puzzle', 'Other'),
PRIMARY KEY (GameId)
);

CREATE TABLE Results(
PlayerId INT,
GameId INT,
Time DATETIME,
Score INT,
PRIMARY KEY (PlayerId,GameId,Time),
CONSTRAINT ResultsGameFK FOREIGN KEY (GameId) REFERENCES Game (GameId),
CONSTRAINT ResultsPlayerFK FOREIGN KEY (PlayerId) REFERENCES Player (PlayerId)
);

当前编写的SQL只能查询所有玩家在各游戏中的分数记录，无法精准定位每个游戏的最高分记录：

Select Game.Name, Game.Category, PlayerCode, Score, Time
from  Player
JOIN Results
ON Player.PlayerId = Results.PlayerId
JOIN Game
ON Game.GameId = Results.GameId 
group by Game.Name, Game.Category, Score, PlayerCode, Time
order by Score DESC

解决方案 #

方法一：使用窗口函数（推荐，适用于MySQL 8.0+/PostgreSQL等支持窗口函数的数据库） #

窗口函数可以按游戏分组排序，直接筛选出每个游戏的最高分记录：

SELECT 
    g.Name AS 游戏名称,
    g.Category AS 游戏类别,
    p.PlayerCode AS 玩家标识,
    r.Score AS 最高分,
    r.Time AS 记录时间
FROM (
    SELECT 
        *,
        -- 按游戏分组，分数降序排，同分则取最新记录
        RANK() OVER(PARTITION BY GameId ORDER BY Score DESC, Time DESC) AS score_rank
    FROM Results
) r
JOIN Player p ON r.PlayerId = p.PlayerId
JOIN Game g ON r.GameId = g.GameId
WHERE r.score_rank = 1;

• PARTITION BY GameId：把数据按游戏拆分，每个游戏单独计算排名
• RANK()：如果多个玩家拿了同一个游戏的最高分，会返回所有同分记录；如果只想要一条，换成ROW_NUMBER()即可

方法二：使用子查询关联（兼容低版本MySQL） #

先统计每个游戏的最高分，再关联回原表拿到对应的玩家和时间信息：

SELECT 
    g.Name AS 游戏名称,
    g.Category AS 游戏类别,
    p.PlayerCode AS 玩家标识,
    r.Score AS 最高分,
    r.Time AS 记录时间
FROM Results r
JOIN Player p ON r.PlayerId = p.PlayerId
JOIN Game g ON r.GameId = g.GameId
-- 子查询先算出每个游戏的最高分
JOIN (
    SELECT GameId, MAX(Score) AS max_score
    FROM Results
    GROUP BY GameId
) ms ON r.GameId = ms.GameId AND r.Score = ms.max_score;

• 子查询ms负责提取每个游戏的最高分
• 通过GameId和Score关联，筛选出所有等于该游戏最高分的记录，同分情况下会返回所有对应玩家的记录

内容的提问来源于stack exchange，提问作者WorkInProgress