如何基于钱包金额计算玩家排名？多表联查排行榜实现

需求背景 #

我正在开发一款基于两张数据表（darkrp_player与playerinformation）联查的玩家排行榜，计划以wallet（钱包）字段作为排名依据，按金额从高到低为玩家添加排名列。当前使用的SQL查询语句如下：

$result = mysqli_query($conn, "SELECT * FROM darkrp_player inner join playerinformation on (darkrp_player.uid = playerinformation.uid)");

最优实现方案 #

1. MySQL 8.0+版本（推荐） #

直接利用窗口函数生成排名，性能和可读性都更优：

SELECT 
    dp.*, 
    pi.*,
    -- 相同钱包金额的玩家共享排名，后续排名会跳过（例：两个第1名，下一个为第3名）
    RANK() OVER(ORDER BY dp.wallet DESC) AS `rank`
    -- 若需要相同金额不跳过排名，替换为DENSE_RANK()即可
    -- DENSE_RANK() OVER(ORDER BY dp.wallet DESC) AS `rank`
FROM darkrp_player dp
INNER JOIN playerinformation pi ON dp.uid = pi.uid
ORDER BY dp.wallet DESC;

2. MySQL 5.x兼容方案 #

如果使用的MySQL版本低于8.0，用用户变量计算排名：

SELECT 
    dp.*, 
    pi.*,
    @rank := @rank + 1 AS `rank`
FROM darkrp_player dp
INNER JOIN playerinformation pi ON dp.uid = pi.uid
CROSS JOIN (SELECT @rank := 0) AS rank_init
ORDER BY dp.wallet DESC;

要处理相同金额排名相同的场景，调整逻辑如下：

SELECT 
    dp.*, 
    pi.*,
    CASE 
        WHEN @prev_wallet = dp.wallet THEN @rank
        ELSE @rank := @rank + 1
    END AS `rank`,
    @prev_wallet := dp.wallet
FROM darkrp_player dp
INNER JOIN playerinformation pi ON dp.uid = pi.uid
CROSS JOIN (SELECT @rank := 0, @prev_wallet := NULL) AS rank_init
ORDER BY dp.wallet DESC;

额外优化建议 #

• 避免使用SELECT *，明确指定需要的字段，减少不必要的数据传输：

  SELECT 
      pi.username,
      dp.wallet,
      RANK() OVER(ORDER BY dp.wallet DESC) AS `rank`
  FROM darkrp_player dp
  INNER JOIN playerinformation pi ON dp.uid = pi.uid
  ORDER BY dp.wallet DESC;
  

• 给darkrp_player.uid和playerinformation.uid字段添加索引，能大幅提升联查的查询效率。

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