SQL实现公会玩家排名分段计数、总积分计算及公会排名

现有mp_rankings表存储玩家赛季排名数据，表结构包含4个字段：

• guildname：公会名
• rank：玩家个人排名
• name：玩家名
• season：赛季编号

样例数据如下：

根据玩家个人rank值为所属公会计算积分，规则如下：

• 排名在1~150区间（含两端）：每名玩家计4分
• 排名在151~300区间（含两端）：每名玩家计2分
• 排名在301~600区间（含两端）：每名玩家计1分

最终输出需包含各分段人数、公会总积分、公会总排名，样例结果结构如下：

原有代码存在4个核心问题：

1. 301~600分段边界判断错误，原条件写为rank >=300，会把属于151~300分段的rank=300的玩家错误归为1分档
2. CTE中多余的group by和order by：单条玩家记录本身是唯一粒度，不需要额外分组，CTE内排序无实际意义还会损耗性能
3. 最终查询分组逻辑错误，按guildname,points分组会把同一公会拆成多条不同积分档的记录，无法直接聚合出单公会的全量统计值
4. 缺少分段人数统计列、总积分聚合逻辑，以及按总积分生成公会排名的逻辑

修正后的可直接运行的SQL如下：

WITH player_point_calc AS (
    SELECT
        guildname,
        -- 提前标记每个玩家所属分段的人数计数、对应积分贡献
        CASE WHEN `rank` BETWEEN 1 AND 150 THEN 1 ELSE 0 END AS cnt_1to150,
        CASE WHEN `rank` BETWEEN 151 AND 300 THEN 1 ELSE 0 END AS cnt_151to300,
        CASE WHEN `rank` BETWEEN 301 AND 600 THEN 1 ELSE 0 END AS cnt_301up,
        CASE WHEN `rank` BETWEEN 1 AND 150 THEN 4 ELSE 0 END AS point_1to150,
        CASE WHEN `rank` BETWEEN 151 AND 300 THEN 2 ELSE 0 END AS point_151to300,
        CASE WHEN `rank` BETWEEN 301 AND 600 THEN 1 ELSE 0 END AS point_301up
    FROM mp_rankings
    WHERE season = 10 -- 提前过滤目标赛季，减少计算量
)
SELECT
    guildname,
    SUM(cnt_1to150) AS rank1to150,
    SUM(cnt_151to300) AS rank151to300,
    SUM(cnt_301up) AS rank301up,
    SUM(point_1to150 + point_151to300 + point_301up) AS sumofpoints,
    -- 按总积分降序生成公会排名，需要积分相同并列排名可替换为DENSE_RANK()
    ROW_NUMBER() OVER (ORDER BY SUM(point_1to150 + point_151to300 + point_301up) DESC) AS `rank`
FROM player_point_calc
GROUP BY guildname
ORDER BY sumofpoints DESC;

代码逻辑说明：

• 通过条件判断逐行标记每个玩家的分段归属和积分贡献，避免分组拆分数据
• 聚合阶段直接按公会分组，一次计算出三个分段的人数、总积分
• 用窗口函数直接生成公会排名，不需要额外嵌套子查询
• 修正了原代码的分段边界错误，统计结果符合规则要求

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