MySQL查询使用ORDER BY时添加连续序号的问题求助

你的查询出现序号混乱，是因为MySQL的执行顺序导致的：@a:=@a+1是在分组计算后、排序前生成的，而ORDER BY net_worth DESC是最后执行的操作——相当于先给未排序的分组结果加序号，再打乱排序，自然序号和最终顺序不匹配。

需要先完成分组计算和排序，再给有序的结果集添加序号，有两种实现方式：

方式一：使用变量（兼容MySQL 5.x及更早版本） #

先通过子查询得到按net_worth降序排列的用户净资产数据，再在外层查询中生成连续序号：

SELECT @a:=@a+1 serial_number, user_name, net_worth
FROM (
    -- 子查询：先计算每个用户的净资产并排序
    SELECT users.user_name,
           TRUNCATE(((SUM(transactions.t_qty*stocks.curr_price))+users.balance),4) as net_worth
    FROM transactions 
    NATURAL JOIN stocks 
    NATURAL JOIN users
    GROUP BY user_email 
    ORDER BY net_worth DESC
) AS ranked_users,
-- 初始化序号变量
(SELECT @a:= 0) AS init_var;

方式二：使用窗口函数（MySQL 8.0及以上版本推荐） #

用ROW_NUMBER()窗口函数可以更简洁地实现，无需手动维护变量：

SELECT 
    ROW_NUMBER() OVER(ORDER BY net_worth DESC) serial_number,
    user_name,
    net_worth
FROM (
    -- 子查询：先计算每个用户的净资产
    SELECT users.user_name,
           TRUNCATE(((SUM(transactions.t_qty*stocks.curr_price))+users.balance),4) as net_worth
    FROM transactions 
    NATURAL JOIN stocks 
    NATURAL JOIN users
    GROUP BY user_email
) AS ranked_users;

两种方式的核心逻辑一致：先确保数据已经按net_worth降序排列，再给每一行分配连续序号，这样序号就能和最终的排序结果完全对应。

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