MySQL 5.7中SUM+ORDER BY场景下正确生成行号的方法

这个问题我之前也踩过坑！核心原因是你当前的SQL里，@row_number变量是在分组之后、排序之前就计算完成了，而ORDER BY是SQL语句最后执行的步骤——它只是把最终结果重新排列，但不会修改已经生成的序号，所以序号自然和你想要的降序顺序对不上。

修正思路 #

我们需要把「分组求和+排序」和「生成行号」拆成两步：

1. 先通过子查询完成用户积分的分组计算，并且直接按总积分降序排好序
2. 基于这个已经排好序的结果集，再用变量生成正确的行号

修正后的SQL语句 #

SELECT 
    user_id,
    user_hash,
    (@row_number := @row_number + 1) AS num,
    total_point
FROM (
    -- 第一步：先计算每个用户的总积分，并按积分降序排序
    SELECT 
        users.user_id,
        users.user_hash,
        SUM(user_stats.total_point) AS total_point
    FROM user_stats
    LEFT JOIN users ON users.user_id = user_stats.stats_user_id
    WHERE user_stats.create_date BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 23:59:59'
    GROUP BY users.user_id, users.user_hash
    ORDER BY total_point DESC
) AS ranked_users,
(SELECT @row_number := 0) AS row_init;

关键细节说明 #

• 内层子查询：先完成GROUP BY计算总积分，同时用ORDER BY total_point DESC把结果按积分从高到低排好，这一步是确保后续生成序号的基础。
• 外层查询：基于已经排序好的结果集，逐行累加@row_number变量，这样生成的num列就会严格对应1、2、3、4...的降序序列。
• 额外注意：分组时把users.user_hash也加入GROUP BY，是为了兼容SQL的ONLY_FULL_GROUP_BY模式（很多数据库默认开启），避免非聚合列的分组报错。

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