MySQL 5.7无RANK函数时按总分+最新时间计算用户排名

正确SQL实现 #

SELECT
    u.id AS user_id,
    u.name,
    user_stats.total_value,
    user_stats.latest_created_at,
    -- 计算排名：统计优先级高于当前用户的用户数量，加1得到最终排名
    (
        SELECT COUNT(*)
        FROM (
            SELECT
                user_id,
                SUM(value) AS total,
                MAX(created_at) AS latest
            FROM scores
            GROUP BY user_id
        ) AS other_users
        WHERE other_users.total > user_stats.total
           OR (other_users.total = user_stats.total AND other_users.latest > user_stats.latest_created_at)
    ) + 1 AS user_rank
FROM
    users u
JOIN (
    -- 预计算每个用户的总分和最新得分记录时间
    SELECT
        user_id,
        SUM(value) AS total_value,
        MAX(created_at) AS latest_created_at
    FROM scores
    GROUP BY user_id
) AS user_stats ON u.id = user_stats.user_id
ORDER BY user_rank ASC;

逻辑说明 #

1. 基础数据整理：通过user_stats子查询计算每个用户的总分（total_value）和最新得分记录的时间（latest_created_at），这是排名计算的基础。
2. 排名核心逻辑：子查询统计所有满足以下条件的用户数量：
   • 总分比当前用户更高
   • 总分相同，但最新得分记录的时间比当前用户更晚
     统计结果加1即为当前用户的排名（排名从1开始）。
3. 关联用户表：最终关联users表获取用户名称，按排名升序排列，符合需求的排序规则。

原SQL的问题分析 #

1. HAVING子句逻辑错误：原SQL中(SELECT SUM(scores2.value) FROM scores AS scores2)计算的是所有分数的总和，而非当前用户的总分，导致筛选条件完全失效。
2. 排名计算逻辑缺失：原SQL的COUNT(*)没有针对「总分相同则按最新时间排序」的规则做统计，无法正确区分同分用户的排名顺序。

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