MySQL查询gameresults表顶级玩家及指定用户单场排名

没问题，我来帮你搞定这两个SQL查询需求，分两部分详细说明：

一、查询获胜场次最多的顶级玩家列表及参赛总场次 #

首先，我们需要统计每个玩家的获胜次数（即status=1的记录数）和总参赛场次（每个玩家在表中的总记录数，因为每局对战会插入两行，所以记录数直接对应参赛次数），然后筛选出获胜次数最多的玩家。这里用窗口函数RANK()可以轻松处理多个玩家并列第一的情况：

SELECT 
    user_id,
    win_count,
    total_games
FROM (
    SELECT 
        user_id,
        -- 统计获胜次数：仅当status=1时计数
        COUNT(CASE WHEN status = 1 THEN 1 END) AS win_count,
        -- 统计总参赛场次：该用户的所有记录数
        COUNT(*) AS total_games,
        -- 按获胜次数降序排名，并列玩家排名相同
        RANK() OVER (ORDER BY COUNT(CASE WHEN status = 1 THEN 1 END) DESC) AS player_rank
    FROM gameresults
    GROUP BY user_id
) ranked_players
-- 筛选排名第一的玩家
WHERE player_rank = 1;

代码说明： #

• 内层子查询通过GROUP BY user_id聚合每个玩家的数据，用CASE表达式精准统计胜场数；
• RANK()窗口函数会给获胜次数最多的玩家标记为排名1，若有多个玩家胜场数相同，都会保留在结果中；
• 外层查询只取排名第一的玩家，同时展示他们的胜场数和总参赛场次。

二、查询指定用户在指定对局中的排名 #

针对指定对局（比如game_id=1），我们需要根据玩家的status值排序（胜利>平局>失败），然后找到目标用户（比如user_id=2）的排名。这里用DENSE_RANK()更合理，因为相同战绩的玩家应该共享同一排名，且后续排名不会跳跃：

SELECT 
    user_id,
    player_rank
FROM (
    SELECT 
        user_id,
        status,
        -- 按status降序排名，相同status的玩家排名一致
        DENSE_RANK() OVER (ORDER BY status DESC) AS player_rank
    FROM gameresults
    -- 指定目标对局
    WHERE game_id = 1
) game_ranks
-- 指定目标用户
WHERE user_id = 2;

代码说明： #

• 内层子查询仅筛选game_id=1的对局数据，用DENSE_RANK()按status降序排名（1胜场排最前，0平局次之，-1败场最后）；
• 如果需要对同战绩的玩家再按user_id排序，可以修改窗口函数的排序规则：ORDER BY status DESC, user_id ASC；
• 外层查询直接筛选目标用户user_id=2，返回他的排名。

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