如何通过SQL获取每个GROUP_ID对应的最高PLAYER_SCORE及玩家信息

方法一：使用窗口函数（推荐，适配多数现代数据库） #

窗口函数能给每个群组内的记录按分数排序，直接筛选出每个组里排名第一的最高分记录。

WITH ranked_players AS (
    SELECT 
        A.GROUP_ID, 
        B.PLAYER_SCORE, 
        A.PLAYER_EMAIL, 
        A.PLAYER_NUMBER,
        ROW_NUMBER() OVER (PARTITION BY A.GROUP_ID ORDER BY B.PLAYER_SCORE DESC) AS rank_num
    FROM PLAYER_INFO A
    JOIN SCORE_INFO B ON B.PLAYER_NUMBER = A.PLAYER_NUMBER AND B.GROUP_ID = A.GROUP_ID
    JOIN ID_INFO C ON A.GROUP_ID = C.GROUP_ID
)
SELECT GROUP_ID, PLAYER_SCORE, PLAYER_EMAIL, PLAYER_NUMBER
FROM ranked_players
WHERE rank_num = 1;

• PARTITION BY A.GROUP_ID：按群组划分数据范围
• ORDER BY B.PLAYER_SCORE DESC：每个群组内按分数从高到低排序，最高分排第1位
• rank_num = 1：只保留每个群组里排名第一的记录，也就是对应最高分的玩家信息

方法二：子查询取每组最高分后关联 #

先查出每个群组的最高分数，再把这个结果和原表关联，匹配到对应的玩家信息。

SELECT 
    A.GROUP_ID, 
    B.PLAYER_SCORE, 
    A.PLAYER_EMAIL, 
    A.PLAYER_NUMBER
FROM PLAYER_INFO A
JOIN SCORE_INFO B ON B.PLAYER_NUMBER = A.PLAYER_NUMBER AND B.GROUP_ID = A.GROUP_ID
JOIN ID_INFO C ON A.GROUP_ID = C.GROUP_ID
JOIN (
    SELECT GROUP_ID, MAX(PLAYER_SCORE) AS max_score
    FROM SCORE_INFO
    GROUP BY GROUP_ID
) AS group_max ON B.GROUP_ID = group_max.GROUP_ID AND B.PLAYER_SCORE = group_max.max_score;

注意：如果同一个群组里有多个玩家分数相同且都是最高分，这个方法会返回所有这些玩家的记录；如果只需要其中一条，优先用方法一。

为什么你之前用MAX会出现重复？ #

你直接加MAX(B.PLAYER_SCORE)但没做正确的关联：如果只写SELECT GROUP_ID, MAX(PLAYER_SCORE), PLAYER_EMAIL... GROUP BY GROUP_ID，多数数据库会报错（因为PLAYER_EMAIL不在分组字段里，也不是聚合函数）；即使某些数据库允许执行，也会随机返回一个玩家的信息，而非对应最高分的那个。必须把“每组最高分”这个条件和原记录做关联，才能拿到准确的玩家信息。

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