MySQL实现按rank计数排序的游戏全局用户排行榜SQL方案

背景与表结构 #

我开发的游戏包含10个世界，每个世界设有20个关卡，现有一张ranks表，结构及示例数据如下：

表约束：user_id,world_num,level_num为唯一组合，rank字段表示用户在对应关卡的排名。

需求 #

生成全局用户排行榜，排序规则为：rank1数量最多的用户排首位；若rank1数量相同，则比较rank2数量，以此类推。

之前的尝试（无法满足需求） #

曾尝试以下SQL语句，但无法实现按各rank数量优先级排序的需求：

SELECT `user_id`, `rank`, COUNT(`rank`) AS 'rank_count'
FROM score
GROUP BY `rank`

问题在于该语句未按用户聚合，且无法按rank1、rank2...的数量优先级排序。

解决方案SQL #

方案1：固定rank范围的静态SQL（适用于已知rank最大值的场景） #

假设游戏中rank最大值为10，可通过条件聚合统计各用户的rank数量，并按优先级排序：

SELECT 
    user_id,
    SUM(CASE WHEN rank = 1 THEN 1 ELSE 0 END) AS rank1Count,
    SUM(CASE WHEN rank = 2 THEN 1 ELSE 0 END) AS rank2Count,
    SUM(CASE WHEN rank = 3 THEN 1 ELSE 0 END) AS rank3Count,
    SUM(CASE WHEN rank = 4 THEN 1 ELSE 0 END) AS rank4Count,
    SUM(CASE WHEN rank = 5 THEN 1 ELSE 0 END) AS rank5Count,
    -- 可根据实际rank范围继续添加后续rank的统计字段
    CONCAT(
        user_id,
        ' (rank1Count = ', SUM(CASE WHEN rank = 1 THEN 1 ELSE 0 END),
        CASE WHEN SUM(CASE WHEN rank = 2 THEN 1 ELSE 0 END) > 0 THEN CONCAT(', rank2Count = ', SUM(CASE WHEN rank = 2 THEN 1 ELSE 0 END)) ELSE '' END,
        CASE WHEN SUM(CASE WHEN rank = 3 THEN 1 ELSE 0 END) > 0 THEN CONCAT(', rank3Count = ', SUM(CASE WHEN rank = 3 THEN 1 ELSE 0 END)) ELSE '' END,
        -- 按需添加后续rank的展示逻辑
        ')'
    ) AS rank_summary
FROM ranks
GROUP BY user_id
ORDER BY 
    rank1Count DESC,
    rank2Count DESC,
    rank3Count DESC,
    rank4Count DESC,
    rank5Count DESC
    -- 排序条件需与统计字段一一对应，按rank从小到大优先级排序
-- LIMIT 100; -- 可按需限制返回的排行榜数量

方案2：动态适配rank范围的SQL（适用于rank范围不确定的场景） #

如果游戏中rank的范围不固定，可使用动态SQL自动适配所有存在的rank值（以MySQL为例）：

-- 生成统计字段、排序条件和展示文本的片段
SET @rank_list = (SELECT GROUP_CONCAT(DISTINCT rank ORDER BY rank) FROM ranks);
SET @select_fields = (SELECT GROUP_CONCAT(CONCAT('SUM(CASE WHEN rank = ', rank, ' THEN 1 ELSE 0 END) AS rank', rank, 'Count') SEPARATOR ', ') FROM (SELECT DISTINCT rank FROM ranks ORDER BY rank) AS r);
SET @order_fields = (SELECT GROUP_CONCAT(CONCAT('rank', rank, 'Count DESC') SEPARATOR ', ') FROM (SELECT DISTINCT rank FROM ranks ORDER BY rank) AS r);
SET @display_parts = (SELECT GROUP_CONCAT(CONCAT('CASE WHEN rank', rank, 'Count > 0 THEN CONCAT('', rank', rank, 'Count = '', rank', rank, 'Count) ELSE '''' END') SEPARATOR ' ') FROM (SELECT DISTINCT rank FROM ranks ORDER BY rank) AS r);

-- 拼接完整SQL语句
SET @sql = CONCAT(
    'SELECT 
        user_id,
        ', @select_fields, ',
        CONCAT(
            user_id,
            '' (rank1Count = '', rank1Count,
            ', @display_parts, ',
            '')
        ) AS rank_summary
    FROM ranks
    GROUP BY user_id
    ORDER BY ', @order_fields
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

预期结果示例 #

1. userId92 (rank1Count = 60)
2. userId45 (rank1Count = 57, rank2Count = 46, rank5Count = 8)
3. userId12 (rank1Count = 57, rank2Count = 40, rank3Count = 17)
4. userId76 (rank1Count = 55, rank3Count = 20, rank4Count = 80)
5. userId33 (rank1Count = 55, rank4Count = 25, rank5Count = 35)
...

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