如何通过SQL直接查询指定玩家在排序结果列表中的排名索引

方案1：支持窗口函数的数据库（MySQL 8.0+/MariaDB 10.2+等，推荐） #

直接使用ROW_NUMBER()窗口函数按你指定的排序规则生成排名，再筛选目标用户即可，性能最优：

-- 查找指定用户ID对应的排名
WITH ranked_player_list AS (
    SELECT
        u.id,
        ROW_NUMBER() OVER (
            ORDER BY u.total_prestige DESC, u.total_level DESC, u.total_xp DESC, s.created ASC
        ) AS rank
    FROM hs_users u
    INNER JOIN hs_userskill s ON u.id = s.userId
    WHERE s.id = (SELECT MAX(id) FROM hs_userskill WHERE userId = u.id)
)
SELECT rank FROM ranked_player_list WHERE id = 目标用户ID;

说明：这里把原查询的IN子查询换成了等值匹配，逻辑等价，执行效率更高。

方案2：低版本数据库（不支持窗口函数） #

使用用户变量迭代计算排名：

SELECT rank FROM (
    SELECT
        u.id,
        @cur_rank := @cur_rank + 1 AS rank
    FROM hs_users u
    INNER JOIN hs_userskill s ON u.id = s.userId
    CROSS JOIN (SELECT @cur_rank := 0) AS var_init
    WHERE s.id = (SELECT MAX(id) FROM hs_userskill WHERE userId = u.id)
    ORDER BY u.total_prestige DESC, u.total_level DESC, u.total_xp DESC, s.created ASC
) AS ranked_result
WHERE id = 目标用户ID;

性能优化建议 #

• 可给hs_users表添加联合索引idx_rank_sort(total_prestige DESC, total_level DESC, total_xp DESC)，大幅降低排序开销
• hs_userskill表的userId字段已有外键关联索引，无需额外添加

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