SQL使用GROUP BY查询每游戏每关卡最高分对应玩家问题求解

问题原因 #

现有SQL仅在关联时匹配了gameid和levelid维度的最高分，未对当前玩家的成绩是否等于该分组最高分做筛选，因此每个游戏+关卡分组下的所有玩家都会携带对应分组的最高分，导致返回多余结果。

解法1（兼容所有标准SQL数据库） #

在原有逻辑的关联条件中补充成绩匹配规则，同时可移除冗余的GROUP BY语句：

SELECT 
  g.gameid, 
  l.levelid, 
  p.playerid, 
  ts.top_score AS score
FROM scores s
INNER JOIN games g ON g.gameid = s.gameid
INNER JOIN levels l ON l.levelid = s.levelid
INNER JOIN player p ON p.playerid = s.playerid
INNER JOIN (
  SELECT gameid, levelid, MAX(score) AS top_score 
  FROM scores 
  GROUP BY gameid, levelid
) ts ON s.gameid = ts.gameid 
  AND s.levelid = ts.levelid 
  AND s.score = ts.top_score

解法2（适用于支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

使用ROW_NUMBER()窗口函数按游戏+关卡分组、分数倒序排序，直接取每组排名第一的记录即可，逻辑更简洁：

WITH ranked_scores AS (
  SELECT 
    s.gameid,
    s.levelid,
    s.playerid,
    s.score,
    ROW_NUMBER() OVER (PARTITION BY s.gameid, s.levelid ORDER BY s.score DESC) AS rn
  FROM scores s
)
SELECT 
  g.gameid,
  l.levelid,
  r.playerid,
  r.score
FROM ranked_scores r
INNER JOIN games g ON g.gameid = r.gameid
INNER JOIN levels l ON l.levelid = r.levelid
INNER JOIN player p ON p.playerid = r.playerid
WHERE r.rn = 1

两种方案返回结果均符合预期：

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